Re: Query Performance
Hello, My query is like this Select a.field1, a.field2, a.field3From (Select a.field1, b.field2, c.field3 From table1 a Join table2 b on b.something = a.something Join table3 c On c.something = a.something Where a.field7 = 'value' UNION ALL Select a.field4, a.field5, a.field6 From table11 a Join table21 b On b.something = a.something Where a.field8 = 'something' ) aJoin table10 bOn b.field11 = (Select c.field11 From table10 c Where c.field10 = a.field1 ) <- instead of a.field1, if I hardcode value (eg. '100') query runs fasterJoin table21 cOn c.something = a.something... In the above query, If I substitute a value for a.field1, query runs faster. Any suggestion/guidance/links to improve the query performance without substituting the value ? Happiness Always BKR Sivaprakash
Re: get user info on log
Hi, You can use log_line_prefix in postgresql.conf to log the user name into the logs. Regards, Ganesh Korde. On Fri, 16 Sep 2022, 6:31 pm Marcos Pegoraro, wrote: > Em qui., 15 de set. de 2022 às 12:59, Adrian Klaver < > adrian.kla...@aklaver.com> escreveu: > >> test(5432)=# set role maura; >> ERROR: role "maura" does not exist >> test(5432)=# SET SESSION AUTHORIZATION 'maura'; >> ERROR: role "maura" does not exist >> >> No, I was asking about an error occurring later, not on set session > authorization command. > > set role maura; > --user Maura exists and set was done correctly > --now I´m working as Maura > select 1/0; -- I would like to see this exception on log being logged as > maura > > thanks > Marcos > >
Re: Extension rpath issues on MacOS
> On Sep 16, 2022, at 14:20, Tom Lane wrote: > IOW: I think they want you to get that from macports or homebrew instead. > It certainly works a lot easier if you do. Yeah, I installed openjdk8 using MacPorts, but it puts its stuff in a rather idiosyncratic location. I guess it's "create-a-symlink" time. Thank you!
Re: Extension rpath issues on MacOS
Christophe Pettus writes: > Has anyone encountered this situation (not libjvm in particular, just an > external library using @rpath) during an extension build? Yeah ... IIRC, I've hit that from trying to use the Apple-supplied libpython to underlie PL/Python. I've not found a workaround. I suspect that Apple doesn't want people using these OS components from "outside", and this is something they're intentionally doing to prevent it. IOW: I think they want you to get that from macports or homebrew instead. It certainly works a lot easier if you do. regards, tom lane
Extension rpath issues on MacOS
I'm attempting to build the pgspider JDBC foreign data wrapper on MacOS, and not having an enormously successful time. The driver source is at: https://github.com/pgspider/jdbc_fdw It (unsurprisingly) needs to link with libjvm.dylib, so I've included the path to it in the PostgreSQL ./configure LDFLAGS. (It's being built outside of contrib/ using PGXS.) It compiles and installs successfully, but can't find libjvm at runtime: j=# create extension jdbc_fdw; ERROR: could not load library "/usr/local/pgsql/lib/jdbc_fdw.so": dlopen(/usr/local/pgsql/lib/jdbc_fdw.so, 10): Library not loaded: @rpath/libjvm.dylib Referenced from: /usr/local/pgsql/lib/jdbc_fdw.so Reason: image not found That's not a big surprise, because the .so uses @rpath in its path to libjvm: $ otool -L /usr/local/pgsql/lib/jdbc_fdw.so /usr/local/pgsql/lib/jdbc_fdw.so: /usr/local/pgsql/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.14.0) @rpath/libjvm.dylib (compatibility version 1.0.0, current version 1.0.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1281.100.1) I could patch it with `install_name_tool`, but I'd like to keep the Makefile intact and non-MacOS-specific. Ideally, I'd like it use an absolute path there. Has anyone encountered this situation (not libjvm in particular, just an external library using @rpath) during an extension build?
Re: New message in PostgreSQL log regarding socket for statistics collector
Christoph, Thank you for your explanation. That puts my mind at ease. Regards, Karin From: Christoph Moench-Tegeder Sent: Friday, September 16, 2022 3:43 PM To: Hilbert, Karin Cc: pgsql-general@lists.postgresql.org Subject: Re: New message in PostgreSQL log regarding socket for statistics collector ## Hilbert, Karin (i...@psu.edu): > 2022-09-16 02:00:16 EDT [1918984]: [7-1] db=,user= LOG: listening on Unix > socket "/tmp/.s.PGSQL.5432" > 2022-09-16 02:00:17 EDT [1918984]: [8-1] db=,user= LOG: test message did not > get through on socket for statistics collector > 2022-09-16 02:00:17 EDT [1918984]: [9-1] db=,user= LOG: trying another > address for the statistics collector > I'm concerned about the "test message did not get through on socket > for statistics collector" & "trying another address for the statistics > collector" messages. That's the stats collector socket, which is used to communicate runtime statistic updates inside the server. As a user, you'll never interact with this socket directly. It's created on the first working address which the system returns as "localhost". The server will try all the "localhost" addresses until the socket can be created and passes the test message, logging messages similar to your message for each failure. In your case the test message was not received (or it was received too late - the server waits only 0.5 seconds for the test message). In any case, a working stats socket was created on your machine: without a working stats socket server startup will fail. (You should be able to see that socket in each PostgreSQL process' file descriptors: UDP, localhost, high port, "connected" to the very same address/port tuple). Why did the test message fail on your first socket? Maybe there's some filtering in place, or your system was very slow and missed the 500ms timeout, or the address was otherwise unusable (not available in that namespace, trying to use IPv4/IPv6 when not configured... I'd have to test which condition results in failure at this stage). Regards, Christoph -- Spare Space
Re: New message in PostgreSQL log regarding socket for statistics collector
## Hilbert, Karin (i...@psu.edu): > 2022-09-16 02:00:16 EDT [1918984]: [7-1] db=,user= LOG: listening on Unix > socket "/tmp/.s.PGSQL.5432" > 2022-09-16 02:00:17 EDT [1918984]: [8-1] db=,user= LOG: test message did not > get through on socket for statistics collector > 2022-09-16 02:00:17 EDT [1918984]: [9-1] db=,user= LOG: trying another > address for the statistics collector > I'm concerned about the "test message did not get through on socket > for statistics collector" & "trying another address for the statistics > collector" messages. That's the stats collector socket, which is used to communicate runtime statistic updates inside the server. As a user, you'll never interact with this socket directly. It's created on the first working address which the system returns as "localhost". The server will try all the "localhost" addresses until the socket can be created and passes the test message, logging messages similar to your message for each failure. In your case the test message was not received (or it was received too late - the server waits only 0.5 seconds for the test message). In any case, a working stats socket was created on your machine: without a working stats socket server startup will fail. (You should be able to see that socket in each PostgreSQL process' file descriptors: UDP, localhost, high port, "connected" to the very same address/port tuple). Why did the test message fail on your first socket? Maybe there's some filtering in place, or your system was very slow and missed the 500ms timeout, or the address was otherwise unusable (not available in that namespace, trying to use IPv4/IPv6 when not configured... I'd have to test which condition results in failure at this stage). Regards, Christoph -- Spare Space
RE: Bind Parameter is Too Big
After working between Appeon for Powerbuilder and SAP for Sybase ASE 15.7, I finally figured out the issue. In the ODBC Driver Administrator, if I configure the postgres ODBC driver and go to Datasource, the Max Varchar limit is 255 by default. I changed this to 1000 and now everything works. I will note, during my testing with Appeon, I discovered via DBtrace that all of my varchar columns report with twice the length of the actual amount a characters in them, when piping from Sybase ASE 15.7 to Postgres. Appeon experienced the same thing, but didn't have an explanation. SAP didn't have an explanation either. -Original Message- From: Tom Lane Sent: Thursday, September 01, 2022 4:03 PM To: Kluzak, Matthew C. Cc: Adrian Klaver ; pgsql-general@lists.postgresql.org Subject: Re: Bind Parameter is Too Big * CAUTION: This email originated from an outside source. Do not click links or open attachments unless you know they are safe. * "Kluzak, Matthew C." writes: > The Sybase databases use > Character Set = 1, iso_1 > ISO 8859-1 (Latin-1) - Western European 8-bit character set. > Sort Order = 50, bin_iso_1 > Binary ordering, for the ISO 8859/1 or Latin-1 character set ( > iso_1). > The Postgres database uses UTF8 Hmmm ... it's plausible that something somewhere is figuring that ISO-8859-1 conversion to UTF8 could expand the data at most 2X, and that's where the 1000-to-2000 multiplier is coming from. I wonder if it would help to set things up so that the client side is sending ISO-8859-1 to Postgres (ie, client_encoding = latin1) and the encoding conversion happens on the server side. In principle it shouldn't matter where the conversion happens, but you might be dealing with some bug or underdocumented limitation in whatever is doing that conversion on the client side. regards, tom lane This transmission, email and any files transmitted with it, may be: (1) subject to the Attorney-Client Privilege, (2) an attorney work product, or (3) strictly confidential under federal or state law. If you are not the intended recipient of this message, you may not use, disclose, print, copy or disseminate this information. If you have received this transmission in error, notify the sender (only) and delete the message. This message may also be subject to disclosure under the North Dakota Open Records Laws.
New message in PostgreSQL log regarding socket for statistics collector
I'm working on an implementation of PostgreSQL 13.8 on a Linux RHEL8 sandbox environment. The server & PostgreSQL have all been configured. I started PostgreSQL & got the following messages in the log file: 2022-09-16 02:00:16 EDT [1918984]: [3-1] db=,user= LOG: starting PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit 2022-09-16 02:00:16 EDT [1918984]: [4-1] db=,user= LOG: listening on IPv4 address "0.0.0.0", port 5432 2022-09-16 02:00:16 EDT [1918984]: [5-1] db=,user= LOG: listening on IPv6 address "::", port 5432 2022-09-16 02:00:16 EDT [1918984]: [6-1] db=,user= LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2022-09-16 02:00:16 EDT [1918984]: [7-1] db=,user= LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-09-16 02:00:17 EDT [1918984]: [8-1] db=,user= LOG: test message did not get through on socket for statistics collector 2022-09-16 02:00:17 EDT [1918984]: [9-1] db=,user= LOG: trying another address for the statistics collector 2022-09-16 02:00:17 EDT [1918986]: [1-1] db=,user= LOG: database system was shut down at 2022-09-14 17:26:53 EDT 2022-09-16 02:00:17 EDT [1918984]: [10-1] db=,user= LOG: database system is ready to accept connections I'm concerned about the "test message did not get through on socket for statistics collector" & "trying another address for the statistics collector" messages. I've never seen these before & wasn't able to find anything about them via a Google search. What I did find, talked about the unix socket directories. I left that parameter at the default setting: #unix_socket_directories = '/var/run/postgresql, /tmp'# comma-separated list of directories I checked both of those locations & found the following entries: [postgres@xx ~]$ ls -al /var/run/postgresql/ total 4 drwxr-xr-x 2 postgres postgres 80 Sep 16 02:00 . drwxr-xr-x 30 root root 900 Aug 31 13:17 .. srwxrwxrwx 1 postgres postgres 0 Sep 16 02:00 .s.PGSQL.5432 -rw--- 1 postgres postgres 67 Sep 16 02:00 .s.PGSQL.5432.lock [postgres@xx ~]$ cat /var/run/postgresql/.s.PGSQL.5432.lock 1918984 /var/lib/pgsql/13/data 1663308016 5432 /var/run/postgresql [postgres@xx ~]$ ls -al /tmp/ | grep .s.PGSQL srwxrwxrwx 1 postgres postgres 0 Sep 16 02:00 .s.PGSQL.5432 -rw--- 1 postgres postgres 52 Sep 16 02:00 .s.PGSQL.5432.lock [postgres@xx ~]$ cat /tmp/.s.PGSQL.5432.lock 1918984 /var/lib/pgsql/13/data 1663308016 5432 /tmp Is there anything that I should be concerned about regarding these messages or are they normal? Also, should I change the unix_socket_directories parm to just have one directory? In past versions of PostgreSQL, we've just had one directory: #unix_socket_directories = '/tmp' Thanks, Karin Karin Hilbert Database Administration Pennsylvania State University
Re: get user info on log
Em qui., 15 de set. de 2022 às 12:59, Adrian Klaver < adrian.kla...@aklaver.com> escreveu: > test(5432)=# set role maura; > ERROR: role "maura" does not exist > test(5432)=# SET SESSION AUTHORIZATION 'maura'; > ERROR: role "maura" does not exist > > No, I was asking about an error occurring later, not on set session authorization command. set role maura; --user Maura exists and set was done correctly --now I´m working as Maura select 1/0; -- I would like to see this exception on log being logged as maura thanks Marcos
Re: Re[2]: CVE-2022-2625
Laurenz Albe writes: > On Thu, 2022-09-15 at 11:19 -0400, Tom Lane wrote: >> I'm a little bemused by your fixation on this particular CVE, >> though. As such things go, it's not a very big deal. > A lot of times, requests like that come from a brainless kind of > institutionalized security: we have to install all software updates > that say "CVE". Never mind that username = password and > the application is running with a superuser. Indeed :-(. But we've issued several CVEs since 9.5 went out of support --- notably, I'd say CVE-2022-1552 from the previous minor-release cycle is a good deal more dangerous than this one. So, again, why worry about -2625 in particular? I'm still wondering whether the OP's installation is even on 9.5.latest; if not, they've likely got even more serious things to worry about. A quick troll through the 9.5.x release notes finds a lot of bugs... regards, tom lane
