Pg client certificate auth
Hello, I am trying to deploy a PG db with client certificate auth. I have read the documentation, but I have a few questions. One of my goals is to be able to use two different CAs and as far as I see there is only one ssl_ca_file, I have tried to concatenate my certs as cert chain and use them, it seems to be working. Since it's not fully documented, do you think this approach won't cause any problems in the future? Generally, I need this because when I have multiple pg servers (primary and standby) I need to use SSL. So PG requires the standby represents a valid client cert, but the client cert ca I need to use for the standby can be different from the client cert ca that will be issuing the other certs that I will be giving to the standard users. Thanks, Duygu
Re: SQL ERROR subquery uses ungrouped column in PostgreSQL
On Thu, 1 May 2025 at 00:29, PALAYRET Jacques wrote: > => In the subquery, the semantic analysis of the query considers the column " > dat " instead of the expression " to_char(dat,'mm') ", which is actually > a grouped column. > Is this normal? How can I simply resolve the problem? Calculate the dat column in a subquery in the outer-level query. Something like: FROM (select *,to_char(dat, 'MM') as strdat from table1) table1 JOIN table2 USING (num_poste) then use strdat in all the places you're currently using to_char(dat, 'MM') David
Index not used in certain nested views but not in others
Dear List, I know how tedious mails with a subject of the type "I don't understand what the planner does" are, but on this one I'm really stumped. Regrettably, the situation is also a bit complex. Hopefully, someone will bear with me. So, in a PostgreSQL 15.12 I have a view over a single table with ~20 columns (the only relevant columns here are the ones that somehow contain "pub[lisher]_did", the others are just there for context; I'm going to call these "pubdids" from here on in the prose): CREATE OR REPLACE VIEW ivoa.obs_radio AS SELECT main.obs_publisher_did, main.s_resolution_min, main.s_resolution_max, NULL::real AS s_fov_min, [...] FROM emi.main (emi.main is a physical table). There is another view made up of about 20 tables, looking somewhat like this: CREATE OR REPLACE VIEW ivoa.obscore AS SELECT 'image'::text AS dataproduct_type, NULL::text AS dataproduct_subtype, 2::smallint AS calib_level, 'PPAKM31'::text AS obs_collection, [...] 'ivo://org.gavo.dc/~?'::text || gavo_urlescape(maps.accref) AS obs_publisher_did, [...] FROM ppakm31.maps UNION ALL [lots of similar definitions] UNION ALL SELECT ssa.ssa_dstype AS dataproduct_type, NULL::text AS dataproduct_subtype, [...] ssa.ssa_pubdid AS obs_publisher_did, [...] FROM dfbsspec.ssa UNION ALL [and still more] The dfbsspec.ssa in this definition is another view: CREATE OR REPLACE VIEW dfbsspec.ssa AS SELECT q.accref, q.owner, [...] q.ssa_pubdid, [...] FROM ( SELECT raw_spectra.accref, [...] raw_spectra.pub_did AS ssa_pubdid, [...] FROM dfbsspec.raw_spectra LEFT JOIN dfbsspec.platemeta ON platemeta.plateid = raw_spectra.plate) q raw_spectra finally is a physical table that has an index: "raw_spectra_pub_did" btree (pub_did) The first view, ivoa.obs_radio, is just a few hundred records, dfbsspec.raw_spectra is about 23 Megarows, the total ivoa.obscore is about 100 MRows which occasionally change, so materialising it is *really* unattractive. The pubdids are strings of about 40 characters. You may argue that this whole system looks a bit insane, but of course this is part of a large metadata handling suite, and all these views are, in some sense, more or less automatic adaptations to different metadata schemes, and dramatic simplifications are at least not entriely trivial. So, can you assume for the moment that I can't get rid of the nested views? Now, when I say EXPLAIN ANALYZE SELECT COUNT(*) FROM ivoa.obscore JOIN ivoa.obs_radio USING (obs_publisher_did); I get: Finalize Aggregate (cost=5114082.70..5114082.71 rows=1 width=8) (actual time=22595.715..22731.950 rows=1 loops=1) [...] -> Parallel Append (cost=0.56..4800918.33 rows=19267799 width=40) (actual time=1.566..18985.964 rows=15410027 loops=5) -> Parallel Index Only Scan using phot_r_pkey on phot_r (cost=0.56..754384.72 rows=5118036 width=32) (actual time=0.854..7995.762 rows=10197024 loops=2) Heap Fetches: 0 [...and lot more of these that have simple pubdid indexes on plain tables, the point being: Postgres *does* use pubdid indexes...] -> Subquery Scan on "*SELECT* 13" (cost=0.00..2685028.32 rows=5803266 width=58) (actual time=0.142..7554.269 rows=4642657 loops=5) -> Parallel Seq Scan on raw_spectra (cost=0.00..2626995.66 rows=5803266 width=756) (actual time=0.137..6841.379 rows=4642657 loops=5) [... and a few more seqscans where there's no index on the pubdid because they are small, and one or two similar cases] My problem is: I can't seem to figure out why Postgres chooses to ignore the pubdid index on raw_spectra.pub_did and instead does the time-consuming seqscan. I thought maybe the genetic optimiser has kicked in because of the large number of tables and SELECTs in there and chose a suboptimal plan. But switching off the genetic optimiser doesn't change the plan. Trying to investigate more closely, I wanted to simplify the situation and created a view like ivoa.obscore but only having the evil table in it: CREATE TEMPORARY VIEW bla AS (SELECT [...] CAST(ssa_pubdid AS text) AS obs_publisher_did, [...] FROM dfbsspec.ssa) When I then say EXPLAIN ANALYZE SELECT COUNT(*) FROM ivoa.obs_radio JOIN bla USING (obs_publisher_did); the query plan looks like this: Aggregate (cost=4873.00..4873.01 rows=1 width=8) (actual time=2.484..2.486 rows=1 loops=1) -> Nested Loop (cost=0.56..4871.60 rows=561 width=0) (actual time=2.478..2.479 rows=0 loops=1) -> Seq Scan on main (cost=0.00..52.61 rows=561 width=48) (actual time=0.011..0.317 rows=561 loops=1
Re: Index not used in certain nested views but not in others
On Wed, Apr 30, 2025 at 3:51 PM Markus Demleitner < msdem...@ari.uni-heidelberg.de> wrote: > Dear List, > > I know how tedious mails with a subject of the type "I don't understand > what the planner does" are, but on this one I'm really stumped. > Regrettably, the situation is also a bit complex. Hopefully, someone > will bear with me. > > So, in a PostgreSQL 15.12 I have a view over a single table with ~20 > columns (the only relevant columns here are the ones that somehow > contain "pub[lisher]_did", the others are just there for context; I'm > going to call these "pubdids" from here on in the prose): > [snip] > Is there anything that would explain that behaviour given I've switched > off the genetic optimiser and postgres has hopefully exhaustively > searched the space of plans in both cases? > Are the tables regularly analyzed and vacuumed? (The default autovacuum analyze threshold of 20% is pretty high.) -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: alter system appending to a value
On Wed, Apr 30, 2025 at 7:15 AM Luca Ferrari wrote: > Rationale: I'm using ansible to configure, thru different steps, > PostgreSQL instances and I would like to have every part to append its > configuration on the previous one. > Ansible is good for bringing your systems to a known consistent state, so your best bet would be to maintain the list of libraries inside Ansible and then simply apply it. If you really don't know what might be in the database, I would have Ansible read the current value, build the new one if the desired library is not there, and apply the new value. Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support
alter system appending to a value
Hi all, as trivial as it sounds, is there a smart way to use ALTER SYSTEM to append to a value? Something like: ALTER SYSTEM shared_preloaded_libraries = current_setting( 'shared_preloaded_libraries' ) || ',foo'; Rationale: I'm using ansible to configure, thru different steps, PostgreSQL instances and I would like to have every part to append its configuration on the previous one. Thanks, Luca
Re: alter system appending to a value
ср, 30 апр. 2025 г. в 14:15, Luca Ferrari : > as trivial as it sounds, is there a smart way to use ALTER SYSTEM to > append to a value? > Something like: ALTER SYSTEM shared_preloaded_libraries = > current_setting( 'shared_preloaded_libraries' ) || ',foo'; > I would do smth like: SELECT format( 'ALTER SYSTEM SET shared_preload_libraries = %L;', setting ) FROM pg_settings WHERE name = 'shared_preload_libraries' \gexec Of course, you should add new value to the existing setting, making sure there are no duplicates and the format is correct. -- Victor Yegorov
SQL ERROR subquery uses ungrouped column in PostgreSQL
Hello, I have an unexpected error in my following query (of course, the query has been simplified here to request help): SELECT num_poste, to_char(dat, 'MM')::integer dat, CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint AS num_decade , CASE WHEN ( CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint IN (1 , 2) AND count(glot)=10 ) THEN ( SELECT valeur FROM table3 WHERE num_serie_coef=CASE WHEN num_poste <9600 THEN 0 WHEN num_poste BETWEEN 9710 and 9889 THEN num_poste/10 END AND coef = 'APRIME_ANGSTROM' AND num_mois=substr( to_char(dat,'mm') , 5, 2)::smallint ) ELSE NULL END AS toto FROM table1 JOIN table2 USING (num_poste) GROUP BY num_poste, to_char(dat, 'MM') , CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint ORDER BY num_poste, to_char(dat, 'MM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint ERROR : subquery uses ungrouped column "table1.dat" from outer query LIGNE 10 : AND num_mois=substr( to_char(dat,'mm') , 5, 2)::smallint ^ => In the subquery, the semantic analysis of the query considers the column " dat " instead of the expression " to_char(dat,'mm') ", which is actually a grouped column. Is this normal? How can I simply resolve the problem? Sincerely.
Re: Index not used in certain nested views but not in others
On Wed, 2025-04-30 at 17:02 -0400, Tom Lane wrote: > Markus Demleitner writes: > > So, when the SELECT statement on dfbsspec.ssa stands along in the view > > definition, Postgres does the right thing; when the exact same query > > stands in a UNION ALL with other tables, Postgres doesn't use the > > index. Hu? > > It's hard to be sure when you've shown us no table definitions and > only fragments of the view definitions. But I suspect what is > happening here is that the view's UNIONs are causing a data type > coercion of raw_spectra.pub_did before it gets to the top level > of the view output. That might interfere with the planner's ability > to see that the outer query's join operator is compatible with > the table's index. For a more detailed description of that problem, see https://www.cybertec-postgresql.com/en/union-all-data-types-performance/ Yours, Laurenz Albe
Re: Index not used in certain nested views but not in others
Markus Demleitner writes: > So, when the SELECT statement on dfbsspec.ssa stands along in the view > definition, Postgres does the right thing; when the exact same query > stands in a UNION ALL with other tables, Postgres doesn't use the > index. Hu? It's hard to be sure when you've shown us no table definitions and only fragments of the view definitions. But I suspect what is happening here is that the view's UNIONs are causing a data type coercion of raw_spectra.pub_did before it gets to the top level of the view output. That might interfere with the planner's ability to see that the outer query's join operator is compatible with the table's index. regards, tom lane
