Error when pasting function blocks into psql
Hello: I noticed that I'm getting errors when copy and pasting code for stored procedure from Windows directly into psql running in putty. To reproduce create a procedure that has multiple statements with each statements having multiple lines, similar to the ones below: CREATE OR REPLACE PROCEDURE test()AS$$ UPDATE table1 SET field1 = ?; -- more SQL statements$$LANGUAGE SQL; Copy and paste from Windows editor (or IDE) to a psql running in a Putty. Chances are it will complain of syntax error. The error doesn't appear when I copy and paste it to an empty file in vim, and the run: psql -f Is there a way of prevent this kind of error when pasting directly into psql? I'm using the latest version of PostgreSQL (server and client. Version 14.4) Thank you in advance,Ludwig Lim
Re: Error when pasting function blocks into psql
On Friday, July 8, 2022 at 10:20:52 PM GMT+8, Tom Lane wrote: Ludwig Isaac Lim writes: >> Hello: >> I noticed that I'm getting errors when copy and pasting code for stored >> procedure from Windows directly into psql running in putty. >> To reproduce create a procedure that has multiple statements with each >> statements having multiple lines, similar to the ones below: >> CREATE OR REPLACE PROCEDURE test()AS$$ UPDATE table1 >> SET field1 = ?; >> -- more SQL statements$$LANGUAGE SQL; >> Copy and paste from Windows editor (or IDE) to a psql running in a Putty. >> Chances are it will complain of syntax error. The error doesn't appear when >> I copy and paste it to an empty file in vim, and the run: >> psql -f >> Is there a way of prevent this kind of error when pasting directly into psql? >A gotcha I frequently run into is that if there are any literal tab > characters in the text, pasting it into psql fails because the tabs >invoke tab-completion, producing either no text or unwanted text. >I don't know if that's an issue on Windows (does your psql build >do tab completion?), but if so, the workaround is to disable tab >completion by starting psql with the '-n' switch. >Another fairly likely possibility is that your paste contains >invisible characters that psql does not think are whitespace, >like non-break space ( ). However, pasting into a file >wouldn't make that problem go away, so the tab idea seems >a shade more likely. > regards, tom lane I indent using tabs, so I believe this is a more likely explanation. I'll try to use the -n switch and see if the error still occurs. Thank you, Ludwig Lim
Strange behavior between timestamp and date comparison
Hello:
Below is a sample case that exhibits a behavior that I can't explain:
-- create the table
create table ts (t timestamp without time zone);
-- populate
insert into ts(t) values ('2022-07-16 00:22:06.974000');
insert into ts(t) values ('2022-07-16 00:22:06.974000');
insert into ts(t) values ('2022-07-16 00:22:06.974000');
-- This one return expected results
select * from ts where t::date between '2022-07-16'::Date - make_interval(days
=> 30) and '2022-07-16'::Date;
t
-
2022-07-16 00:22:06.974
2022-07-16 00:22:06.974
2022-07-16 00:22:06.974
(3 rows)
-- This one doesn't return anything (unexpected)
select * from ts where t between '2022-07-16'::Date - make_interval(days =>
30) and '2022-07-16'::Date;
t
---
(0 rows)
-- version
select version();
version
-
PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712
(Red Hat 7.3.1-15), 64-bit
(1 row)
Regards,
Ludwig Lim
print in plpython not appearing in logs
I'm having problems wherein my print() statements inside my plpython stored
proc are not appearing in postgresql log. I tried setting the file=sys.stderr
To reproduce:
CREATE OR REPLACE PROCEDURE p_ludwig_test()
AS
$$
import sys
try:
x = 1 / 0
except:
plpy.log("hello")
print("oink oink", file=sys.stderr)
print("oink oink - v2")
plpy.log("haha")
$$
LANGUAGE plpython3u;
call p_ludwig_test()
Output:
-
2022-12-02 11:46:11.324 UTC [19390] LOG: hello
2022-12-02 11:46:11.324 UTC [19390] CONTEXT: PL/Python procedure
"p_ludwig_test"
2022-12-02 11:46:11.324 UTC [19390] STATEMENT: call p_ludwig_test();
2022-12-02 11:46:11.324 UTC [19390] LOG: haha
2022-12-02 11:46:11.324 UTC [19390] CONTEXT: PL/Python procedure
"p_ludwig_test"
2022-12-02 11:46:11.324 UTC [19390] STATEMENT: call p_ludwig_test();
Notice that the "oink oink" is not there.
Relevant logging configuration:
logging_collector = on
log_directory = 'logs'
log_min_messages = info
log_min_error_statement = error
PG version
---
PostgreSQL 14.6 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-15), 64-bit
The reason why I'm trying to use print() is because plpy logger is quite
verbose. Each logging will output 3 line
for example:
plpy.log("hello")
Will generate the following 3 lines in the log:
2022-12-02 11:46:11.324 UTC [19390] LOG: hello
2022-12-02 11:46:11.324 UTC [19390] CONTEXT: PL/Python procedure
"p_ludwig_test"
2022-12-02 11:46:11.324 UTC [19390] STATEMENT: call p_ludwig_test();
Another thing is there is another stored procedure that I have wherein the
print() to stderr actually works, so I'm not sure what I'm doing wrong.
Thank you in advance,
Ludwig
ERROR : cannot commit while a portal is pinned from plpython
Hello:
I noticed the following lines in my PostgreSQL log file today:
2022-12-01 14:16:54.517 UTC [10073] ERROR: cannot commit while a portal is
pinned
2022-12-01 14:16:54.517 UTC [10073] STATEMENT: call p_annotate_db_stats()
The process ID can be traced to plpython procedure call.
What is baffling is there's no commit (or rollback) inside the stored procedure
call.
The notable changes I made to the stored procedure is:
adding this block of code
try:
--processing stuff
except:
exc_type, exc_value, tb = sys.exc_info()
if tb is None:
print("No exception / traceback information", file=sys.stderr)
sys.exit(-1)
prev = tb
curr = tb.tb_next
while curr is not None:
prev = curr
curr = curr.tb_next
print(prev.tb_frame.f_locals, file=sys.stderr)
traceback.print_exception(*sys.exc_info())
print("Exiting", file=sys.stderr)
The stored procedure is executed via psql call using the following command in
bash:
nohup psql -c "call p_annotate_db_stats()" &
Postgresql version : PostgreSQL 14.6 on aarch64-unknown-linux-gnu, compiled by
gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-15), 64-bit
Python version : Python 3.7.10
What could be the cause of the error? Thank you.
Regards,
Ludwig Lim
Re: print in plpython not appearing in logs
Hi Tom:
>> I'm having problems wherein my print() statements inside my plpython stored
>> proc are not appearing in postgresql log. I tried setting the file=sys.stderr
> Hmm. I can tell you that with "logging_collector = on", I would only expect
> the logs to capture stderr output, not stdout. So it makes
> sense to me that plain "print" would disappear into the bit bucket. But if
> you specify stderr output, it ought to work. I don't know
> enough Python to know why it's not working, but it seems to me this is
> primarily a Python question not a Postgres question. Maybe you need
> an explicit fflush-equivalent step? Dunno.
> regards, tom lane
As usual, thank you for pointing out where problem lies.
I made it work by adding flush=True flag to the print() statement.
print("oink oink", file=sys.stderr, flush=True)
I was under the wrong impression that output to stderr are automatically
flushed.
Thank you once again.
Regards,
Ludwig
Network Card Not Listening at Startup
Hello: I'm running postgresql 12.9 on Amazon Linux in AWS. My listen_addresses in postgresql.conf is configured like below: listen_addresses = '172.17.1.57,localhost' However, postgresql does not listen on 172..17.1.57. I can reproduce the phenomenon by simply rebooting the server and then run netstat -tulpn [root@ip-172-17-1-206 data]# netstat -tulpn | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 2605/postgres And there's nothing abnormal in the logs: 2022-02-09 15:59:36.906 UTC [2663] LOG: database system was shut down at 2022-02-09 15:59:20 UTC 2022-02-09 15:59:36.930 UTC [2605] LOG: database system is ready to accept connections I had to run: systemctl restart postgresql For 172.17.1.57 to listen [root@ip-172-17-1-206 data]# netstat -tulpn | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 4289/postgres tcp 0 0 172.17.1.57:5432 0.0.0.0:* LISTEN 4289/postgres Note that 172.17.1.57 is not the primary network interface. I created the network interface and attached it to the EC2 instance. My systemd config file for postgresql (/etc/systemd/system/postgresql.service) was copied from 18.3. Starting the Database Server 18.3. Starting the Database Server 18.3. Starting the Database Server 18.3.1. Server Start-up Failures 18.3.2. Client Connection Problems Before an... Extra information -- postgres=# select version(); version PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (R (1 row) How do make sure that NIC will be listening every time I started/restarted the server? Thanks in advance, Ludwig Lim
Re: Network Card Not Listening at Startup
> On Thursday, February 10, 2022, 01:49:10 AM GMT+8, Tom Lane > wrote: >> Ludwig Isaac Lim writes: >> How do make sure that NIC will be listening every time I started/restarted >> the server? > You need to tell systemd not to start the postgresql service > until the network is up. > In basic cases, you can edit the postgresql.service file, > or maybe better create an add-on config file something like this: > $ cat /etc/systemd/system/postgresql.service.d/delay_start.conf > [Unit] > After=network-online.target > Wants=network-online.target > (The add-on file name doesn't matter, the directory name does.) > I'm not certain that this will wait for custom NICs if you are > doing something nonstandard to configure those, but for me > it works for alternate IPs configured in > /etc/sysconfig/network-scripts/ifcfg-whatever. > regards, tom lane Thanks Tom. It works. Regards, Ludwig
