Error Cannot Copy To/from Client In Pl/pgsql
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations User groups Project lists Inactive lists IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly News Re: COPY FROM STDIN fails inside Pl/pgSQL function or in PgAdmin , but ok in psql From: Chris Browne
Reply | Threaded Open this post in threaded view ♦ ♦ | Report Content as Inappropriate ♦ ♦ Interesting COPY edge case... Just ran into a fascinating edge case. One of our folks was building a stored function, and ran into an odd error when trying to COPY to stdout. Here's a characteristic example: create or replace function build_table (integer) returns integer as ' begin execute ''copy foo to stdout;''; return 1; end' language plpgsql; The result: /* cbbrowne@[local]/dba2 in=*/ select try_copy(1); ERROR: unexpected error -2 in EXECUTE of query "copy dups to stdout;" CONTEXT: PL/pgSQL function "try_copy" line 2 at https://www.postgresql.org/message-id/60psrw75j9.fsf@dba2.int.libertyrms.com execute statement If I instead create create or replace function build_table (integer) returns integer as ' copy foo to stdout; return 1; ' language sql; The latter works fine. There is evidently Something Strange about the state of stdout when it is referenced inside a stored procedure. We can work around this reasonably, but the "unexpected error -2" elicits some curiosity I'd like to satisfy. (I http://postgresql.nabble.com/Interesting-COPY-edge-case-td1931789.html bounced it off one of the others that aren't off on vacation, and his reaction was exactly the same as mine, namely "Hmmm... I'm just not sure what to expect from that...") Is there a good reason/excuse to give as to why the pl/pgsql version of the COPY *shouldn't* work? -- (format nil "~S@~S" "cbbrowne" "acm.org") http://www.ntlug.org/~cbbrowne/sap.htmlRules of the Evil Overlord #78. "I will not tell my Legions of Terror "And he must be taken alive!" The command will be: ``And try to take him alive if it is reasonably practical.''"
Summary: COPY FROM STDIN works on psql, but not inside a Pl/pgSQL function, nor a pgAdmin SQL window, and http://osdir.com/ml/db.postgresql.interfaces/2005-08/msg00049.html not on a ODBC command... I would appreciate if anybody could explain http://bajis-postgres.blogspot.com/2014/01/basic-errors-of-postgresql-part1.html me why this doesn't work: -- create table CREATE TABLE temp_data ( xx float, yy float, ww float, hh float) WITHOUT OIDS; -- create function CREATE OR REPLACE FUNCTION check_data(text) RETURNS integer AS $func$ BEGIN EXECUTE 'COPY temp_data(xx,yy,ww,hh) FROM STDIN WITH DELIMITER ''|'';' ||'\n' || error cannot $1 || '\n\\.'; -- just to get something out return length($1); END; $func$ LANGUAGE plpgsql; and then: select check_data('1.0|2.0|3.0|4.0'); It gives out: ERROR: syntax error at or near "1.0" at character 60 QUERY: COPY temp_data(xx,yy,ww,hh) FROM STDIN WITH DELIMITER '|'; 1.0|2.0|3.0|4.0 \. CONTEXT: PL/pgSQL function "check_data" line 8 at execute statement Actually, if I do the COPY within error cannot copy a pgAdmin SQL command prompt, it also fails. For example, the following, alone, without any plpgsql - COPY temp_data(xx,yy,ww,hh) FROM STDIN WITH DELIMITER '|'; 1.0|2.0|3.0|4.0 \. will fail. If on the other hand, I execute these lines on a psql prompt, it works just fine. Any help will be appreciated. Regards Silvio ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Thread at a glance: Previous Message by Date: Re: ecpg: arrays and nulls Hi, Michael Meskes napsal(a): On Tue, Aug 02, 2005 at 10:40:23AM +0200, Kuba Ouhrabka wrote: I'd like to ask how to handle null values when fetching arrays into ecpg. Always I get this error: Data read from backend is not an array in line ... Found it. The check for array data was doen too early. Of course a NULL does not look like an array, so the message was issued before the data was tested on
with those. Mostly this is breaking the things more than fixing, if anyone wants to play with postgres troubleshooting.. ;-) Error1: -------- -bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres psql.bin: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5435"? Cause/Resolution: -------------------- 1. First thing you would need to check is server status(using below commands), if server is not running, start it and try to connect. -bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data/ status pg_ctl: server is running (PID: 49230) /opt/PostgreSQL/9.3/bin/postgres "-D" "../data" -bash-4.1$ ps -ef|grep data postgres 49230 1 0 13:32 pts/1 00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D ../data postgres 57430 27596 0 18:23 pts/1 00:00:00 grep data 2. If you found that cluster is running and still not able to connect, then check the port number in postgresql.conf file and try to connect using correct port. -bash-4.1$ grep -i 'port' /opt/PostgreSQL/9.3/data/postgresql.conf port = 5435 # (change requires restart) -bash-4.1$ ls -ltr /tmp/.s.PGSQL.5435* -rw-------. 1 postgres postgres 51 Jan 12 13:05 /tmp/.s.PGSQL.5435.lock srwxrwxrwx. 1 postgres postgres 0 Jan 12 13:05 /tmp/.s.PGSQL.5435 -bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres Timing is on. psql.bin (9.3.2) Type "help" for help. postgres=# Error2: -------- -bash-4.1$ ./psql -p 5435 -U postgres -h 192.168.225.185 postgres psql: could not connect to server: Connection refused Is the server running on host "192.168.225.185" and accepting TCP/IP connections on port 5435? Cause/Resolution: -------------------- 1. You would need to look at your "listen_addresses" parameter in postgresql.conf file, check if you are set this to allow the other servers to connect. -bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres Timing is on. psql.bin (9.3.2) Type "help" for help. postgres=# show listen_addresses ; listen_addresses ------------------ localhost (1 row) 2. If you found that you set it to allow, then you will have to look at your firewall setting. May be port is not opened for that server due to security issues. Error3: -------- -bash-4.1$ ./psql -p 5435 -U postgres -h 192.168.225.185 post