Re: Transaction issue
Am Wed, Jun 19, 2024 at 02:32:07PM -0700 schrieb Rich Shepard: > Yes, I see how this works if the transaction is committed. But before I > commit the transaction I run a select statement to ensure the rows added are > correct. Can I rollback a commited transaction? I've assumed not, so I won't > commit the transaction without testing. And I'm not getting a detailed error > message. Shot in the dark: are you be any chance using tab-completion when running the SELECT before the COMMIT ? I've seen it happen a few times that tab completion ran queries behind the scenes which failed and thereby set the open transaction to abort state ... And, no, I can't reproduce :/ Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: DROP COLLATION vs pg_collation question
Am Tue, Jun 18, 2024 at 03:02:56PM +0200 schrieb Karsten Hilbert: > I see, and since any database can be used as a template for > more databases, which can be create with an encoding > different from the template, Proving myself wrong: root@hermes:~/tmp# sudo -u postgres psql -e -f /tmp/db.sql Output format is wrapped. drop database if exists new_tmpl; DROP DATABASE create database new_tmpl with encoding = 'utf8'; CREATE DATABASE You are now connected to database "new_tmpl" as user "postgres". select current_database(); current_database -- new_tmpl (1 row) drop database if exists new_latin1; psql:/tmp/db.sql:8: HINWEIS: Datenbank »new_latin1« existiert nicht, wird übersprungen DROP DATABASE create database new_latin1 with template = new_tmpl encoding = 'latin1' locale = 'de_DE@latin1'; psql:/tmp/db.sql:9: FEHLER: neue Kodierung (LATIN1) ist inkompatibel mit der Kodierung der Template-Datenbank (UTF8) TIP: Verwenden Sie die gleiche Kodierung wie die Template-Datenbank oder verwenden Sie template0 als Template. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: DROP COLLATION vs pg_collation question
Am Sun, Jun 16, 2024 at 04:38:49PM -0400 schrieb Tom Lane: > It's really kind of moot, since you can't change the encoding > of an existing database. So any pg_collation entries that are > for an incompatible encoding cannot be used for anything in that > database, and they might as well not be there. The reason they > are there is merely an implementation detail: CREATE DATABASE clones > those catalogs from the single copy of pg_collation in template0, > which therefore had better include all collations that might be > needed. I see, and since any database can be used as a template for more databases, which can be create with an encoding different from the template, it doesn't really make too much sense to be able to remove even pg_collation entries. So, DROP COLLATION is somewhat of a smoking gun pointed at my foot :-) Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: DROP COLLATION vs pg_collation question
Am Sun, Jun 16, 2024 at 06:53:31AM +0200 schrieb Laurenz Albe: > On Fri, 2024-06-14 at 22:08 +0200, Karsten Hilbert wrote: > > Are collations per-database or per-cluster objects ? > > Each database has its own "pg_collation" catalog table. > > So they are local to the database, I would have thought so, thanks for confirming. > but the collations themselves > are defined by an external library, so the implementation is shared. Which in turn means I cannot at all truly _remove_ collations from a cluster at the SQL level, only make them invisible (and thereby not-to-be-used) inside a particular database by removing them from pg_collations via DROP COLLATION, right ? > > 3) update collation version information in pg_collations for > > collations intended for an encoding different from the > > database encoding (ALTER COLLATION ... REFRESH VERSION fails) > > > > which in effect would mean that -- upon change of collation > > versions in the underlying operating system (ICU update, libc > > update) -- one would have to live with outdated version > > information in pg_collations short of dump/sed/restore or > > some such ? > > That should not happen. What error do you get when you > > ALTER COLLATION ... REFRESH VERSION The error I got was to the effect of insufficient permissions (the connected user wasn't the owner of the collation). ALTERing as superuser updated collation version information just fine, so PEBKAC. For the record: Yes, collation versions CAN be updated regardless of whether a given collation applies to the database's encoding. > Does the following give you the same error? > > ALTER DATABASE ... REFRESH COLLATION VERSION That did not show the same error because I ran it as database owner (?). The database owner does not own the collations, however, which made the above fail. Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: DROP COLLATION vs pg_collation question
Am Thu, Jun 13, 2024 at 09:49:46AM +0200 schrieb Laurenz Albe: > > > Note that with DROP COLLATION you can only remove collations > > > that belong to the encoding of your current database. > > src/backend/catalog/namespace.c: > > /* >* get_collation_oid - find a collation by possibly qualified name >* >* Note that this will only find collations that work with the current >* database's encoding. >*/ > Oid > get_collation_oid(List *collname, bool missing_ok) Thanks. Are collations per-database or per-cluster objects ? I am asking because I seem to not be enabled to 1) use a collation that's intended for an encoding different from the database encoding -> makes sense 2) remove a collation that's intended for an encoding different from the database encoding -> so far so good, ignore them, but 3) update collation version information in pg_collations for collations intended for an encoding different from the database encoding (ALTER COLLATION ... REFRESH VERSION fails) which in effect would mean that -- upon change of collation versions in the underlying operating system (ICU update, libc update) -- one would have to live with outdated version information in pg_collations short of dump/sed/restore or some such ? I'm pretty sure I am overlooking something. Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: DROP COLLATION vs pg_collation question
> > DROP COLLATION IF EXISTS pg_catalog."" > > Yes, that will delete a row from "pg_collation". Many thanks. > Note that with DROP COLLATION you can only remove collations > that belong to the encoding of your current database. A-ha ! Can that bit be found anywhere in the docs ? IOW, the following code is exactly useless ? (because of the "collencoding <> _db_encoding" business ;-) create function gm.remove_unneeded_collations() returns void language plpgsql security definer as ' DECLARE _rec record; _db_name text; _db_encoding integer; BEGIN SELECT pg_catalog.current_database() INTO _db_name; SELECT encoding INTO _db_encoding FROM pg_database WHERE datname = _db_name; RAISE NOTICE ''database [%]: removing collations for encodings other than the database encoding [%]'', _db_name, pg_catalog.pg_encoding_to_char(_db_encoding); FOR _rec IN ( SELECT oid, collnamespace, collname, collencoding FROM pg_collation WHERE oid > 1000 AND collencoding IS NOT NULL AND collencoding <> -1 AND collencoding <> _db_encoding ) LOOP RAISE NOTICE ''dropping collation #% "%.%" (encoding: %)'', _rec.oid, _rec.collnamespace::regnamespace, _rec.collname, pg_catalog.pg_encoding_to_char(_rec.collencoding); BEGIN EXECUTE ''DROP COLLATION IF EXISTS '' || _rec.collnamespace::regnamespace || ''."'' || _rec.collname || ''"''; EXCEPTION WHEN undefined_object THEN RAISE NOTICE ''collation does not seem to exist (perhaps for the DB encoding ?)''; END; END LOOP; END;'; The reason for this being the wish to reduce the risk surface for locale version information changes at the OS level by removing collations not relevant to a given database. Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
DROP COLLATION vs pg_collation question
Dear list members, maybe a naive question but I was unable to find an answer in the fine manual (sv_SE being an example) Does running DROP COLLATION IF EXISTS pg_catalog."sv_SE" also remove the corresponding row from pg_collation (assuming nothing depends on collation sv_SE) ? Experimentation seems to hint that way but I was unable to confirm. Thanks Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?
Am Thu, Mar 07, 2024 at 08:04:21PM +0200 schrieb Achilleas Mantzios: > > Python 3.11.2 (main, Mar 13 2023, 12:18:29) [GCC 12.2.0] on linux > > Type "help", "copyright", "credits" or "license" for more information. > > >>> for idx in [0,1,2,3]: print(idx) > > Dude this is like saying that for n=N the statement itself takes O(N) to write You do realize this is a conceptual example ... > that should be avoided at any cost > in 1st year of college. .. which I'd fully expect to be teachable to 1st year of college folks ? However, let's refrain from discussing that _here_. Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?
Am Thu, Mar 07, 2024 at 04:56:18PM +0200 schrieb Achilleas Mantzios - cloud: > Python IMHO is too advanced, too rich, Python _is_ powerful but it needn't be difficult. > weird indentation rules A matter of taste IMO. > no simple for loop Really ? Python 3.11.2 (main, Mar 13 2023, 12:18:29) [GCC 12.2.0] on linux Type "help", "copyright", "credits" or "license" for more information. >>> for idx in [0,1,2,3]: print(idx) ... 0 1 2 3 >>> Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: "reverse" (?) UPSERT -- how to ?
Am Sat, Feb 17, 2024 at 07:25:11PM +0100 schrieb Karsten Hilbert: > +1 except that I've got use for both parts of the UNION on > their own (they are both views themselves involving a bunch > of joins with yet other tables, 4 or 5 each or so ;-) Just for kicks, attached find the SQL change script ... Using a LEFT JOIN would get unwieldy, fast, I guess. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B v23-clin-v_intakes_with_regimens.sql Description: application/sql
Re: "reverse" (?) UPSERT -- how to ?
Am Sat, Feb 17, 2024 at 11:54:05AM -0500 schrieb Greg Sabino Mullane: > > There will be a view giving rows for > > each detail row enriched with master table data > > UNION ALL > > rows for each master row that does not have any detail row with > > detail table columns NULLed > > > > A better way to do that is to just use a LEFT JOIN. +1 except that I've got use for both parts of the UNION on their own (they are both views themselves involving a bunch of joins with yet other tables, 4 or 5 each or so ;-) Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: "reverse" (?) UPSERT -- how to ?
Am Sat, Feb 17, 2024 at 09:33:11AM -0700 schrieb Rob Sargent: > > So, is this doable within one SQL statement (short of > > creating and running the abovementioned function in > > one go ;-) ? > > > > Perhaps your pk_detail can be defined as generated always identity? Rob, I'm sure there's something to be learned from that suggestion the significance of which I don't grasp yet. Would you mind expanding a bit ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: "reverse" (?) UPSERT -- how to ?
Am Sat, Feb 17, 2024 at 08:55:57AM -0800 schrieb Adrian Klaver: > >Dear list members, > > > > >So, is this doable within one SQL statement (short of > >creating and running the abovementioned function in > >one go ;-) ? > > Don't know what version of Postgres you are on, assuming 15+ then maybe?: Ah, sorry, I omitted that detail :( Release target for new installs is current Debian Stable, IOW Bookworm, which has PG 15. postgresql: Installiert: 15+248 Installationskandidat: 15+248 Versionstabelle: 16+257 500 500 https://deb.debian.org/debian testing/main i386 Packages *** 15+248 990 990 https://deb.debian.org/debian bookworm/main i386 Packages 100 /var/lib/dpkg/status However, legacy deployments may run, say PG 13, root@hermes:~/bin# rmadison postgresql postgresql | 11+200+deb10u5 | oldoldstable | all postgresql | 13+225+deb11u1 | oldstable| all postgresql | 15+248 | stable | all postgresql | 16+257 | testing | all postgresql | 16+257 | unstable | all so ideally it would work on that, too. However, it does not seem too much to ask to *upgrade* GNUmed database servers to Debian *Stable* if on plans on running the next, as-yet-unreleased version ;-) > MERGE > > https://www.postgresql.org/docs/current/sql-merge.html :-) I knew I missed something. As Greg also pointed out that is what I need. Thanks ! Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
"reverse" (?) UPSERT -- how to ?
Dear list members, maybe I am overlooking something. PostgreSQL offers UPSERT functionality by way of INSERT INTO ... ON CONFLICT ... DO UPDATE ...; Consider this pseudo-code schema table master pk_master serial primary key value text ; table detail pk_detail serial primary key fk_master int foreign key master(pk_master) detail text ; where there can be any number of rows in detail linking to a particular row in master, including none (master rows without details, that is). There will be a view giving rows for each detail row enriched with master table data UNION ALL rows for each master row that does not have any detail row with detail table columns NULLed What I want to achieve: Given a pk_detail (and pk_master) having been obtained from the view (therefore pk_detail being NULL or an integer value) UPDATE that detail row (namely when pk_detail is distinct from NULL) or insert a new detail row (when pk_detail IS NULL) linking that row to the master row identified by pk_master. I know I can do so from client code. I also know I can wrap this functionality inside a plpgsql function. I am wondering though whether it can be done as one SQL statement. It seems to me that would call for an UPDATE ... ON MISSING ... DO INSERT ...; or CASE WHEN pk_detail IS NULL THEN INSERT ... WHEN pk_detail IS DISTINCT FROM NULL THEN UPDATE ... END both of which don't exist/work, of course. So, is this doable within one SQL statement (short of creating and running the abovementioned function in one go ;-) ? (the real story is about medication and intake regimens / schedules thereof involving a whole lot more columns, of course, which should not be relevant to the problem though) Thanks for taking the time to read, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Safest pgupgrade jump distance
Am Mon, Feb 12, 2024 at 09:31:50AM -0500 schrieb Ron Johnson: > https://www.postgresql.org/docs/16/pgupgrade.html > "pg_upgrade supports upgrades from 9.2.X and later to the current major > release of PostgreSQL, including snapshot and beta releases." Just to be sure: it should be stressed that the binaries of the later version (16 in OPs case) should be used to run the upgrade, right ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: How should we design our tables and indexes
Am Sun, Feb 11, 2024 at 12:53:10PM +0530 schrieb veem v: > >> Pagination is already a hard problem, and does not even make sense when > > combined with "a continuous stream of inserts". What should the user see > > when they click on page 2? > > > > When the user clicks to the second page , it will see the next set of rows > i.e 100 to 200 and next will see 200 to 300 and so on till the result set > finishes. Given a continuous stream of inserts "second page" or "next set of rows" is undefined -- if you aim for live data, because interleaving data may have been inserting while the user inspected the first batch of results. A "second page" is only defined in terms of "what the original query returned on the first run". Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Nested-Internal Functions
> I am currently using PostgreSQL 15 and I am trying to create a nested > function with the following structure: ... > However, I get an error What *is* the error ? Karsten
Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?
Am Sat, Jan 13, 2024 at 05:53:14PM -0500 schrieb Ron Johnson: > *No,* that's a technology problem. What is the purpose of storing them > back in the database using psql? Or even the end goal to be achieved by that ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Time zone offset in to_char()
> > In the above, I worked around the issue using a couple of user-defined > > functions in PG. That should give a reasonable idea of the desired > > functionality, but it's not an ideal solution to my problem: > > 1). The first function has as a drawback that it changes the time zone for > > the entire transaction (not sufficiently isolated to my tastes), while > > 2). The second function has the benefit that it doesn't leak the time zone > > change, but has as drawback that the time zone is now hardcoded into the > > function definition, while > > 3). Both functions need to be created in the caching database before we can > > use them, while we have several environments where they would apply (DEV, > > pre-PROD, PROD). > > Would a function that dispatches its calls to a suitable array of hard-coded > functions based on an IN parameter help any ? Without testing the idea - could it even generate the hardcoded function as needed, based on the parameter, and then run it ? Karsten
Aw: Time zone offset in to_char()
> In the above, I worked around the issue using a couple of user-defined > functions in PG. That should give a reasonable idea of the desired > functionality, but it's not an ideal solution to my problem: > 1). The first function has as a drawback that it changes the time zone for > the entire transaction (not sufficiently isolated to my tastes), while > 2). The second function has the benefit that it doesn't leak the time zone > change, but has as drawback that the time zone is now hardcoded into the > function definition, while > 3). Both functions need to be created in the caching database before we can > use them, while we have several environments where they would apply (DEV, > pre-PROD, PROD). Would a function that dispatches its calls to a suitable array of hard-coded functions based on an IN parameter help any ? Karsten
Aw: Role for just read the data + avoid CREATE / ALTER / DROP
set default_transaction_read_only can help Karsten Gesendet: Freitag, 25. August 2023 um 14:38 Uhr Von: "Durumdara" An: "Postgres General" Betreff: Role for just read the data + avoid CREATE / ALTER / DROP Dear Members! Normally we use the "db owner" role for the connection, but this can do everything (DDL-DML). Somewhere they want to access a DB through a Read Only connection. In MS-SQL Server it is simple, but in PG it seems to be some kind of "hell". Formerly we tried to use multiple roles with lower rights, but we had wrong experiences with them, so we stopped very soon. So: is there any easier way to make ReadOnly access to a database? I've tried with this command: CREATE ROLE u_tdb_ro WITH LOGIN; REVOKE ALL PRIVILEGES ON SCHEMA public FROM u_tdb_ro; REVOKE ALL ON DATABASE tdb FROM u_tdb_ro; GRANT CONNECT ON DATABASE tdb TO u_tdb_ro; GRANT USAGE ON SCHEMA public TO u_tdb_ro; REVOKE ALL ON ALL TABLES IN SCHEMA public FROM u_tdb_ro; REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM u_tdb_ro; REVOKE CREATE ON SCHEMA public FROM u_tdb_ro; REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro; REVOKE CREATE ON TABLESPACE pg_default FROM u_tdb_ro; GRANT SELECT ON ALL TABLES IN SCHEMA public TO u_tdb_ro; ALTER USER u_tdb_ro SET DEFAULT_TRANSACTION_READ_ONLY = ON; ALTER DEFAULT PRIVILEGES FOR role u_tdb GRANT SELECT ON TABLES TO u_tdb_ro; But: I can't avoid that the user can execute a CREATE TABLE command! set role to u_tdb_ro; drop table if exists test_rororo; create table if not exists test_rororo (roro int primary key); select * from test_rororo; I don't understand why these commands don't work?! REVOKE CREATE ON SCHEMA public FROM u_tdb_ro; REVOKE CREATE ON DATABASE tdb FROM u_tdb_ro; REVOKE CREATE ON TABLESPACE pg_default FROM u_tdb_ro; I read that the magic command is: REVOKE CREATE ON SCHEMA public FROM PUBLIC; Why does this work, and why are the 3 above not??? What is the meaning of these 3 if they don't work? Why do the PGSQL developers create these statements if they do not work? I wanna understand this well, to know that Full Read Only mode is possible or not; or what are the limitations. Thank you for lighting my mind! Best regards dd
Aw: Re: Fatal Error : Invalid Memory alloc request size 1236252631
Even I used postgreSQL Large Objects by referring this link to store and retrieve large files (As bytea not working) https://www.postgresql.org/docs/current/largeobjects.html But even now I am unable to fetch the data at once from large objects select lo_get(oid); Here I'm getting the same error message. But if I use select data from pg_large_object where loid = 49374 Then I can fetch the data but in page wise (data splitting into rows of each size 2KB) So, here how can I fetch the data at single step rather than page by page without any error. And I'm just wondering how do many applications storing huge amount of data in GBs? I know that there is 1GB limit for each field set by postgreSQL. If so, how to deal with these kind of situations? Would like to know about this to deal with real time scenarios. https://github.com/lzlabs/pg_dumpbinary/blob/master/README.md might be of help Karsten
Re: question on auto_explain
Am Fri, Aug 04, 2023 at 01:33:19PM +0800 schrieb Julien Rouhaud: > > explicit hint towards write query side effects. > > The docs says that it automatically shows the execution plans, not that it's > itself doing an EXPLAIN. Yep, so maybe _that_ point warrants being pointed out: that auto_explain *doesn't* suffer from plain EXPLAIN write-query "side effects" because it instruments normally-run queries and just outputs to the log extra information (the plans). Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: question on auto_explain
> > auto_explain automatically produces the explain output of a query that is > > running for reals. The effect is identical to running explain analyze > > except your output > here is whatever the query would produce instead of > > the explain output, which instead goes into the log. > > Thanks David. I take this to mean that auto_explain produces the same > side-effects as manually running "explain analyze" does. > > Would this warrant a documentation patch ? Like so: > > auto_explain.log_analyze causes EXPLAIN ANALYZE output, rather than just > EXPLAIN output, to be printed when an execution plan is logged. Hence > the same caveats apply for INSERT/UPDATE/DELETE queries. > > Also, doesn't this makes auto_explain.log_analyze = TRUE rather surprising as > it > can make any INSERT/UPDATE/DELETE fail when it is slow for some reason ... Ah, wait, I think I've been a bit dense here. I thought it was a two-step process of first running any queries "normally", somehow magically noticing slow ones as per auto_explain.log_min_duration, and re-running those with EPXPLAIN ANALYZE prepended. I think I understand better now: with auto_explain ALL queries are run with EXPLAIN ANALYZE prepended BUT the output is two-fold: query results are fed into whatever wire protocol client is and EXPLAIN output is re-routed to the log. Does that sound right ? I think was misguided by psql discarding (?) query results (the rows) while displaying EXPLAIN output only. The auto_explain docs might perhaps still benefit from a more explicit hint towards write query side effects. Karsten
Aw: Re: question on auto_explain
> On Thu, Aug 3, 2023 at 9:29 AM Karsten Hilbert > mailto:karsten.hilb...@gmx.net]> wrote: >> >> >>https://www.postgresql.org/docs/current/auto-explain.html[https://www.postgresql.org/docs/current/auto-explain.html] >> >> don't explicitely state that it does so, too. Nor can I read impliciteness >> that >> "normal" EXPLAIN is *run* by auto_explain. > > auto_explain automatically produces the explain output of a query that is > running for reals. The effect is identical to running explain analyze except > your output > here is whatever the query would produce instead of the explain > output, which instead goes into the log. Thanks David. I take this to mean that auto_explain produces the same side-effects as manually running "explain analyze" does. Would this warrant a documentation patch ? Like so: auto_explain.log_analyze causes EXPLAIN ANALYZE output, rather than just EXPLAIN output, to be printed when an execution plan is logged. Hence the same caveats apply for INSERT/UPDATE/DELETE queries. Also, doesn't this makes auto_explain.log_analyze = TRUE rather surprising as it can make any INSERT/UPDATE/DELETE fail when it is slow for some reason ... Thanks, Karsten
question on auto_explain
Dear list, when debugging slow queries in a larger application (https://www.gnumed.de) I started to use auto_explain. The "normal" EXPLAIN warns https://www.postgresql.org/docs/current/sql-explain.html that ANALYZE on INSERT/UPDATE/DELETE will (of course, in hindsight) modify rows. Now, the auto_explain docs https://www.postgresql.org/docs/current/auto-explain.html don't explicitely state that it does so, too. Nor can I read impliciteness that "normal" EXPLAIN is *run* by auto_explain. Hence my question: Does auto_explain also modify rows on INSERT/UPDATE/DELETE if auto_explain.log_analyze is TRUE ? If not how so ? (I guess it would have to run a dance of "BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;" just before any query is being run.) Thanks, Karsten
Aw: When will trusted PL/Python be supported?
> It seems to me that we have untrusted PL/Python for a long time, but > till now we still do not support trusted plpython. > > I'd like to know is supporting trusted PL/Python still in the > schedule? What is the reason for the current lack of support, and do > we have any relevant email discussion? AFAIR the consensus was that Python cannot really be (made to be) trusted in the PG sense which is why plpython/trusted was *removed*. Karsten
Re: Reproducing incorrect order with order by in a subquery
Am Thu, Jun 15, 2023 at 12:58:55AM +0300 schrieb Ruslan Zakirov: e.g. > https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ ... > My goal was to find a small dataset that demonstrates this ordering > mismatch. I attempted to think it through whether it is even *possible* to find a dataset (of any size) which *reliably* demonstrates the ordering mismatch. To my understanding -- since the outer query *can* (per happenstance) return rows in the order of the subquery's ORDER BY it is not possible to reliably demonstrate that it won't, is it ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Reproducing incorrect order with order by in a subquery
Am Wed, Jun 14, 2023 at 01:03:06PM +0300 schrieb Ruslan Zakirov: > > This is a too complex query to build a test on. Tried simpler scenarios > > and failed. > > > > First of all I want to apologize. We work with multiple RDBMS systems. This > particular user is using mysql. ... > Anyway, yesterday I tried my simplified case on Pg latest, Pg 11 and on > mysql latest. Had no luck. Either my test case is too simple or I can not > find the correct distribution of data between two tables. IOW neither is help to be expected on this list nor can any testing (on PG) help with anything to be expected on MySQL ? As to the question: since the outer query does not have an ORDER BY it can return results in any order INCLUDING the one produced by the subquery. Which renders impossible any *proving* that it can return rows in orders different from the subquery *unless* one forces a different order on the outer query. Which in turn would defeat the purpose as then the outer query *does* have an explicit ordering... IMO you need to either rewrite the query or look to MySQL for more detailed help. Probably the former, regardless of the latter. Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Guidance on INSERT RETURNING order
Am Fri, Apr 14, 2023 at 10:44:19PM +0100 schrieb John Howroyd: > The problem is that SQLAlchemy is an ORM [...] ... > [...] as the majority of the python world will use this ORM for > their database needs. I wouldn't be so sure on this count ... Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres
Am Mon, Apr 10, 2023 at 01:33:41PM +0200 schrieb Francisco Olarte: > > > > This the part that's always eluded me: How does the client, the > > > > UPSERTer, come to hold an id and not know whether or not it's already in > > > > the database. > > > > > > This is extremely easy to do if you have natural instead of surrogate > > > keys. > > > > > > I work in telephony, upserting the last incoming call timestamp for a > > > phone number will be exactly that. > > > > timezones ? > > DST ? > > A timestamp is a point in the time line, this is what I insert, just a > real number marking a line, timezones and dst are presentation stuff. Indeed, as is the assumption which time line the numbers are referring to. Hence the incoming call timestamp is usable as a (natural) PK with respect to a given time line only, right? > > spoofing ? > > ¿ Of what ? The time stamp. But then I assume that is obtained on the logging system. All I really wanted to hint at is that "incoming call timestamp" may work pretty well in given settings but does not _always_ make for a "unique enough" key. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres
Am Mon, Apr 10, 2023 at 09:41:15AM +0200 schrieb Francisco Olarte: > On Mon, 10 Apr 2023 at 04:16, Rob Sargent wrote: > > > An UPSERT checks whether a row exists, if so, it does an update, if not > > > it does an insert. This is the literal definition. > > This the part that's always eluded me: How does the client, the > > UPSERTer, come to hold an id and not know whether or not it's already in > > the database. > > This is extremely easy to do if you have natural instead of surrogate keys. > > I work in telephony, upserting the last incoming call timestamp for a > phone number will be exactly that. timezones ? DST ? spoofing ? ... Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
> >> b...@yugabyte.com wrote: > >> > >> I’ve no idea how I might have found this without human help. > > > > x...@thebuild.com wrote: > > > > That sounds like an excellent documentation patch! > > Well, it’s already documented clearly enough. The question is how to find > it—especially if you don’t know that the feature that you’re looking > for exists or not.The psql doc would print out at about thirty pages with a > normal font size. So reading it from top to bottom would be quite a task. But, then, documentation is there to be read. And it tends to be the longer the more details it is expected to cover, isn't it ? Searching for generic terms on typical search engines can be quite a task, agreed. Karsten
Aw: Re: REINDEX vs VACUUM
> Von: "Peter J. Holzer" > On 2023-01-04 09:34:42 -0600, Ron wrote: > > I don't think VACUUM FULL (copy the table, create new indices and other > > metadata all in one command) actually vacuums tables. It's a misleading > > name. > > Move all the stuff from the living room to the bedroom and then jettison > the living room. > > Isn't that how you normally vacuum your living room? Well, yeah, I wouldn't expect the table to be *copied*. After all, there's not that much use for more than one per living room. Karsten
Re: dropped default locale
Am Wed, Dec 21, 2022 at 03:46:48PM +0100 schrieb Karsten Hilbert: > I wasn't so sure whether inserting appropriate > rows would be equivalent to create collation... For that matter, is DELETE FROM pg_collation ... equivalent to DROP COLLATION ? Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: dropped default locale
Am Wed, Dec 21, 2022 at 03:41:24PM +0100 schrieb Laurenz Albe: > I would definitely go for the backup, but here is how you can > create these three rows in PostgreSQL v15: > > INSERT INTO pg_collation > (oid, collname, collnamespace, collowner, collprovider, > collisdeterministic, collencoding, collcollate, collctype) > VALUES > (100, 'default', 11, 10, 'd', TRUE, -1, NULL,NULL), > (950, 'C', 11, 10, 'c', TRUE, -1, 'C', 'C'), > (951, 'POSIX', 11, 10, 'c', TRUE, -1, 'POSIX', 'POSIX'); Many thanks ! I wasn't so sure whether inserting appropriate rows would be equivalent to create collation... (pg_collation might have been a view projecting inner workings of the server engine). Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
dropped default locale
Dear all, I managed to drop the "special" collations default, C, and POSIX with OIDs 100, 950, 951. Is there a way to recreate them (short of restoring a backup) ? Naive attempts with create collation do not seem to work out. Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: plpgsql_check_function issue after upgrade
Am Mon, Dec 05, 2022 at 03:54:28PM +0530 schrieb shashidhar Reddy:
> Is there any way to get the older version 1.1 of plpgsql_check to install
> it?
Is there any way to get you to respect the kind requests to
follow this list's posting customs as expressed by the very
people you want to help you ?
Such as not to top post.
Best regards,
Karsten
> On Mon, 5 Dec, 2022, 1:36 pm Pavel Stehule, wrote:
>
> >
> >
> > po 5. 12. 2022 v 8:42 odesílatel shashidhar Reddy <
> > shashidharreddy...@gmail.com> napsal:
> >
> >> Pavel,
> >>
> >> Below is the back trace result
> >>
> >> libffi6-dbg libgcc1-dbg libkrb5-dbg libstdc++6-8-dbg libxml2-dbg
> >> postgresql-13-dbgsym postgresql-13-pldeb
> >> ugger-dbgsym zlib1g-dbg
> >>
> >
> > I am sorry, I don't anything
> >
> >
> >
> >>
> >> On Mon, Dec 5, 2022 at 11:28 AM Pavel Stehule
> >> wrote:
> >>
> >>> Hi
> >>>
> >>>
> >>> po 5. 12. 2022 v 6:49 odesílatel shashidhar Reddy <
> >>> shashidharreddy...@gmail.com> napsal:
> >>>
> Pavel,
>
> Where can I get list-dbgsym-packages.sh script?
>
> >>>
> >>>
> >>> https://manpages.debian.org/testing/debian-goodies/find-dbgsym-packages.1.en.html
> >>>
> >>> https://wiki.ubuntu.com/DebuggingProgramCrash
> >>>
> >>> please, don't use top-post style in this mailing list
> >>> https://en.wikipedia.org/wiki/Posting_style
> >>>
> >>> Regards
> >>>
> >>> Pavel
> >>>
> >>>
> >>>
> On Wed, Nov 30, 2022 at 10:04 AM Pavel Stehule
> wrote:
>
> >
> >
> > st 30. 11. 2022 v 5:28 odesílatel Pavel Stehule <
> > pavel.steh...@gmail.com> napsal:
> >
> >>
> >>
> >> st 30. 11. 2022 v 1:38 odesílatel shashidhar Reddy <
> >> shashidharreddy...@gmail.com> napsal:
> >>
> >>> I have tried updating after upgrade but that wasn't working, so I
> >>> have dropped and recreated the extension.
> >>> Now it is crashing every time when we call the function.
> >>>
> >>
> >> what is version od plpgsql_check on Postgres 12, what is version of
> >> plpgsql_check on Postgres 13 (with version of minor release)?
> >>
> >> Can you send backtrace?
> >> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
> >>
> >
> > Do you have installed some other extensions?
> >
> >
> >
> >>
> >>
> >>
> >>
> >>
> >>>
> >>> On Tue, 29 Nov, 2022, 9:58 pm Pavel Stehule, <
> >>> pavel.steh...@gmail.com> wrote:
> >>>
>
>
> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
> > Plogsql check version is 2.2 and one more finding is before
> > calling the function if we drop and recreate the plpgsql_check
> > extension
> > there is no issue, but each time we can't drop and create.
> >
>
> Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before
> first usage in pg 13
>
> If the extension works after re-installation, then the problem is
> not in an extension.
>
>
>
> > On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
> > shashidharreddy...@gmail.com> wrote:
> >
> >> Hello Pavel,
> >>
> >> This is the function causing the issue on all servers, and also i
> >> noticed when I use *plpgsql_check_function *in any function I am
> >> facing the same issue.
> >>
> >>
> >> On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule <
> >> pavel.steh...@gmail.com> wrote:
> >>
> >>> Hi
> >>>
> >>>
> >>> út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
> >>> shashidharreddy...@gmail.com> napsal:
> >>>
> Hello,
>
> Recently we have upgraded postgres from version 12 to 13 and
> upgraded plpgsql_check to the latest version but
> after upgrade when calling the below function causing postgres
> restart .
>
> CREATE OR REPLACE FUNCTION pro.po_check(
> )
> RETURNS void
> LANGUAGE 'plpgsql'
> COST 100
> VOLATILE SECURITY DEFINER PARALLEL UNSAFE
> AS $BODY$
> DECLARE
> BEGIN
>
> PERFORM p.oid, n.nspname, p.proname,
> plpgsql_check_function(p.oid)
> FROM pg_catalog.pg_namespace n
> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
> JOIN pg_catalog.pg_language l ON p.prolang = l.oid
> WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
> and upper(n.nspname) like upper('Pro');
>
> END;
> $BODY$;
>
> and the error in syslogs
Re: Q: error on updating collation version information
Is this to be expected ? PG 15.1 on Debian: gnumed_v22=# select *, pg_collation_actual_version(oid), pg_encoding_to_char(collencoding) from pg_collation where collname = 'zh_TW'; -[ RECORD 1 ]---+ oid | 12985 collname| zh_TW collnamespace | 11 collowner | 10 collprovider| c collisdeterministic | t collencoding| 4 collcollate | zh_TW.euctw collctype | zh_TW.euctw colliculocale | collversion | 2.35 pg_collation_actual_version | 2.36 pg_encoding_to_char | EUC_TW -[ RECORD 2 ]---+ oid | 12986 collname| zh_TW collnamespace | 11 collowner | 10 collprovider| c collisdeterministic | t collencoding| 6 collcollate | zh_TW.utf8 collctype | zh_TW.utf8 colliculocale | collversion | 2.36 pg_collation_actual_version | 2.36 pg_encoding_to_char | UTF8 gnumed_v22=# begin; BEGIN gnumed_v22=*# alter collation pg_catalog."zh_TW" refresh version ; NOTICE: version has not changed ALTER COLLATION gnumed_v22=*# alter collation pg_catalog."zh_TW.utf8" refresh version ; NOTICE: version has not changed ALTER COLLATION gnumed_v22=*# alter collation pg_catalog."zh_TW.euctw" refresh version ; ERROR: collation "pg_catalog.zh_TW.euctw" for encoding "UTF8" does not exist gnumed_v22=!# As far as I can tell the documentation asserts that since the database encoding is UTF8 the pg_catalog."zh_TW.euctw" will be ignored by the server for all practical purposes. Does this mean it is impossible to "correct" its version information ? And if so, that is expected to be non-harmful and is not expected to trigger nag messages ? Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
Am Sun, Dec 04, 2022 at 06:05:30PM +0100 schrieb Alban Hertroys: > > I mean, pg_get_functiondef output being a server runtime artifact it might > > well change between server versions, no ? > > I meant to write: “I would also generate new diffs right > _before and_ after…”, precisely for that reason. I see. That makes sense. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Q: error on updating collation version information
Am Sun, Dec 04, 2022 at 10:09:47AM -0800 schrieb Adrian Klaver: > >>following an ICU upgrade, collations in a stock Debian PG 15.1 > >>cluster now have divergent version information in pg_collations. > > > >Correction: this is following a libc upgrade 2.35 -> 2.36 > > So to be clear this database is not using ICU, but collations from libc? Sorry for the confusion. This database carries collations from _both_ libc and ICU in pg_collations. The collation in question (br_FR@euro) is _not_ in use (as in being depended on by any in-database object). > How was the database installed? stock Debian apt-get install postgresql-15 (which gives 15.1) followed by CREATE DATABASE "gnumed_v22" with owner = "redacted :-)" template = "template1" encoding = 'unicode'; as "postgres". > In first post you had: > > gnumed_v22=> select *, pg_encoding_to_char(collencoding) from pg_collation > where > collname = 'br_FR@euro'; > -[ RECORD 1 ]---+--- > oid | 12413 > collname| br_FR@euro > collnamespace | 11 > collowner | 10 > collprovider| c > collisdeterministic | t > collencoding| 16 > collcollate | br_FR@euro > collctype | br_FR@euro > colliculocale | > collversion | 2.35 > pg_encoding_to_char | LATIN9 > > where collprovider c means libc and collversion 2.35. Yeah, that's when I figured that I misspoke about the ICU upgrade. Yes, there was an ICU upgrade, and yes, it did affect collations. Those I was able to fix up (the "reindex / revalidate constraint / refresh collation version" dance). There also was a libc upgrade which also affected locales. Most of them were fixable by that dance but some popped up (such as br_FR@euro) to not be "correctable" showing the "does not exist for encoding" error. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Q: error on updating collation version information
Am Sun, Dec 04, 2022 at 01:22:02PM +0100 schrieb Karsten Hilbert: > following an ICU upgrade, collations in a stock Debian PG 15.1 > cluster now have divergent version information in pg_collations. Correction: this is following a libc upgrade 2.35 -> 2.36 Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Q: error on updating collation version information
Am Sun, Dec 04, 2022 at 01:22:02PM +0100 schrieb Karsten Hilbert: > gnumed_v22=> ALTER COLLATION pg_catalog."br_FR@euro" REFRESH VERSION; > ERROR: collation "pg_catalog.br_FR@euro" for encoding "UTF8" does not > exist The OS (libc) does seem to know that collation: @hermes:~$ locale -a | grep br_FR br_FR br_FR@euro br_FR.utf8 Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Q: error on updating collation version information
Dear all, following an ICU upgrade, collations in a stock Debian PG 15.1 cluster now have divergent version information in pg_collations. Now gnumed_v22=> ALTER COLLATION pg_catalog."br_FR@euro" REFRESH VERSION; ERROR: collation "pg_catalog.br_FR@euro" for encoding "UTF8" does not exist despite gnumed_v22=> select *, pg_encoding_to_char(collencoding) from pg_collation where collname = 'br_FR@euro'; -[ RECORD 1 ]---+--- oid | 12413 collname| br_FR@euro collnamespace | 11 collowner | 10 collprovider| c collisdeterministic | t collencoding| 16 collcollate | br_FR@euro collctype | br_FR@euro colliculocale | collversion | 2.35 pg_encoding_to_char | LATIN9 However, note the UTF8 vs LATIN9. The manual sayeth: Some (less frequently used) encodings are not supported by ICU. When the database encoding is one of these, ICU collation entries in pg_collation are ignored. Attempting to use one will draw an error along the lines of “collation "de-x-icu" for encoding "WIN874" does not exist”. which sounds somewhat related. The database encoding is UTF8. That br_FR@euro.LATIN9 had _not_ been added manually. It is also not actively used in my database(s). What is the canonical advice on the way forward here ? Is the _suggested_ solution to delete the collation or am I missing to see the "proper" approach to fixing it ? Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
> You would need to wrap the function creation calls into some automation to > generate and store those diffs, comparing it back, etc, but that may be > doable. I would also generate new diffs right after major version updates of > the database (a before and after of the output of pg_get_functiondef, applied > to the stored diff?). I wonder whether that would tie the sanity check to a particular PG version. I mean, pg_get_functiondef output being a server runtime artifact it might well change between server versions, no ? Best, Karsten
Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...
Am Tue, Nov 22, 2022 at 11:59:59PM -0500 schrieb Kirk Wolak: > > It's OK to post a work-in-progress patch to pgsql-hackers, even if it > > doesn't work right yet. With any luck, people will show up to help > > with problems. I am 100% sure that our Windows user community would > > love this feature. It would be good if the tests in > > src/bin/psql/t/010_tab_completion.pl pass on Windows, but if that's > > hard, don't let that stop you sharing a patch. > > > > Thomas, thanks for that! So new to this, I didn't realize... That's a > great idea. > Honestly not sure how to even run it? > > Thanks for the support, it's encouraging... especially when I know there's > an 80% chance that > this may fail to get accepted for any number of reasons. I don't think that estimate needs to be that pessimistic. Thanks for the effort to bring tab completion to psql on windows. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Q: fixing collation version mismatches
Am Mon, Nov 14, 2022 at 05:42:16PM +0100 schrieb Daniel Verite: > > Which is why my question still stands: does the above > > three-strikes operation safely take care of any collation > > issues that may currently exist in a database ? > > For the indexes, yes, but theorically, all constraints involving collatable > types need a recheck. > > For foreign key constraints with non-deterministic collations, there > might be equality tests that pass with an older Unicode version and fail > with a newer Unicode version. Which gives weight to the argument that using real-world data (instead of surrogate keys) may lead to trouble. > For check constraints as well, checks applied to strings with recent > Unicode characters can give different results after an upgrade. Thanks for pointing this out more clearly. My thinking already evolved towards also including VALIDATE CONSTRAINT. I shall, for the record, update the sequence in question: -- indices REINDEX DATABASE db_in_question; -- constraints (check, foreign key) UPDATE pg_constraint SET convalidated = false WHERE all_check_and_FK_constraints; ALTER TABLE table_with_constraint VALIDATE CONSTRAINT constraint_on_that_table; -- other things, see below -- ... -- refresh collation versions if no errors above ALTER DATABASE db_in_question REFRESH COLLATION VERSION; ALTER COLLATION every_collation_from_pg_collation REFRESH VERSION; What else needs to be taken care of, and how ? partitions Need to re-sort rows into the proper partition as needed. Can this be achievd by UPDATE each_partitioned_table SET each_partitioned_key = each_partitioned_key; ? Courtesy of (ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION): Updating the partition key of a row will cause it to be moved into a different partition if it no longer satisfies the partition bounds of its original partition. range types Will this UPDATE table_with_range_type_column SET ranged_column = ranged_column find all relevant issues ? domains Will this UPDATE table_with_domain_type_column SET domained_column = domained_column find all relevant issues ? custom types ?? function immutability ?? It can be argued that functions marked IMMUTABLE really are not in case they involve sorting of a collatable data type, and are thus wrongly marked as IMMUTABLE. IOW pre-existing user error. If all this has been discussed in detail, I'd be glad for a pointer into the archive. Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Q: fixing collation version mismatches
Am Sun, Nov 13, 2022 at 12:46:53PM -0800 schrieb Christophe Pettus: > > On Nov 13, 2022, at 12:45, Karsten Hilbert wrote: > > REINDEX DATABASE db_in_question; > > ALTER DATABASE db_in_question REFRESH COLLATION VERSION; > > ALTER COLLATION every_collation_from_pg_collation REFRESH VERSION; > > I may be totally off-base here, but shouldn't the REINDEX be the last step? To my understanding, the REFRESH statements "merely" update the version information stored in the related objects. They do not change anything else; and the REINDEX does not reference them in any way. I suppose the REINDEX goes first as it does the actual fixing of now-invalid objects by rebuilding them. After that one is back to a usable database state, even if left with pesky (albeit harmless) warnings on version mismatches -- which to get rid of one runs the REFRESH statements. Or so my understanding... Which is why my question still stands: does the above three-strikes operation safely take care of any collation issues that may currently exist in a database ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Q: fixing collation version mismatches
Dear all, just to confirm my understanding: Is it correct to say that the following sequence will "fix" all current collation version issues in a given database ? REINDEX DATABASE db_in_question; ALTER DATABASE db_in_question REFRESH COLLATION VERSION; ALTER COLLATION every_collation_from_pg_collation REFRESH VERSION; Note that I am currently _not_ concerned with minimizing work by running this on objects only that really need a reindex/refresh. Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: Q: documentation improvement re collation version mismatch
> > The comment above the query in the official documentation is rather > > assertive > > (even if may true to the letter) and may warrant some more cautionary > > wording ? Added, perhaps, some variation of this: > > > > > For now, the only safe way to go is either reindex everything, or > > > everything > > > except some safe cases (non-partial indexes on plain-non-collatable > > > datatypes > > > only). > > I think the comment is very poorly worded, as it leads readers to believe that > objects with a pg_depend dependency on a collation are the only one that would > get corrupted in case of glibc/ICU upgrade. > > I agree that there should be a big fat red warning saying something like > "reindex everything if there's any discrepancy between the recorded collation > version and the currently reported one unless you REALLY know what you're > doing." Given that it does not seem straightforward to mechanically detect objects in need of a collation-associated rebuild I would think that such a warning would change matters for the better, documentation-wise. Karsten
Aw: Re: Q: documentation improvement re collation version mismatch
Thanks, Julien, for your explanation.
> > regarding changed collation versions this
> >
> > https://www.postgresql.org/docs/devel/sql-altercollation.html
> >
> > says:
> >
> > The following query can be used to identify all
> > collations in the current database that need to be
> > refreshed and the objects that depend on them:
> >
> > SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS
> > "Collation",
> >pg_describe_object(classid, objid, objsubid) AS "Object"
> > FROM pg_depend d JOIN pg_collation c
> >ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
> > WHERE c.collversion <> pg_collation_actual_version(c.oid)
> > ORDER BY 1, 2;
> >
> > I feel the result of that query can be slightly surprising
> > because it does not return (to my testing) any objects
> > depending on the database default collation, nor the database
> > itself (as per a collation version mismatch in pg_database).
>
> Indeed. The default collation is "pinned", so we don't record any dependency
> on it.
Indirectly we do, don't we ? Or else
> > WHERE
> > collprovider IN ('d', 'c')
would not make much sense, right ?
The comment above the query in the official documentation is rather assertive
(even if may true to the letter) and may warrant some more cautionary
wording ? Added, perhaps, some variation of this:
> For now, the only safe way to go is either reindex everything, or everything
> except some safe cases (non-partial indexes on plain-non-collatable datatypes
> only).
Best,
Karsten
Q: documentation improvement re collation version mismatch
Dear all,
regarding changed collation versions this
https://www.postgresql.org/docs/devel/sql-altercollation.html
says:
The following query can be used to identify all
collations in the current database that need to be
refreshed and the objects that depend on them:
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS
"Collation",
pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d JOIN pg_collation c
ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid)
ORDER BY 1, 2;
I feel the result of that query can be slightly surprising
because it does not return (to my testing) any objects
depending on the database default collation, nor the database
itself (as per a collation version mismatch in pg_database).
Now, there is a line
For the database default collation, there is an analogous
command ALTER DATABASE ... REFRESH COLLATION VERSION.
right above that query but the query comment does not really
make it clear that the database default collation is _not_
identified to be in mismatch, if so. IOW, the database
default collation may still need to be refreshed even if the
query does not return any rows.
Perhaps this query (taken from the net)
SELECT -- get collation-change endangered indices
indrelid::regclass::text,
indexrelid::regclass::text,
collname,
pg_get_indexdef(indexrelid)
FROM (
SELECT
indexrelid,
indrelid,
indcollation[i] coll
FROM
pg_index, generate_subscripts(indcollation, 1)
g(i)
) s
JOIN pg_collation c ON coll=c.oid
WHERE
collprovider IN ('d', 'c')
AND
collname NOT IN ('C', 'POSIX');
could be added to the paragraph (or it could be folded into
the first query by a UNION or some such) ?
Or perhaps one could move the "ALTER DATABASE ... REFRESH
..." hint _below_ the query paragraph and add "Note: you may
need to refresh the default collation even if the query above
does not show any objects directly affected by a collation
version change" ?
Thanks for considering.
Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Information to CVE-2022-42889
> the german bureau for IT-Security "BSI" (Bundesamt für Sicherheit in der > Informationstechnik) has issued a warning for CVE CVE-2022-42889 with the > name commons-text. Insurance companies are obliged to analyse the installed > software for vulnerabilities of this type. As the Barmenia is using your product PostgreSQL Server it is necessary to obtain all information regarding any vulnerability against above CVE. We kindly ask you to provide information if the above product is affected by the CVE and if yes, when a fix will be available. > With the request for short-term feedback. It might be prudent for Barmenia, a large insurance company, to consider purchasing commercial support rather than requesting short-term feedback from volunteers. Other than that there's also excellent documentation and freely inspectable source code. Best regards, Karsten
Aw: Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"
> The client user should *never* read the PostgreSQL configuration files, so if > changing > the permissions (which you should *never* do) has an effect, you must be > doing something > very strange, like trying to start the database server with the wrong user. It smells of trying to *embed* PostgreSQL ? But that would not go with the account of multi-tenancy that's been presented. Karsten
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
Am Sat, Oct 29, 2022 at 09:15:08PM -0700 schrieb Adrian Klaver: > >*# MAPNAME SYSTEM-USERNAME PG-USERNAME* > >*# --- --- --- > > bllewell mary mary > >* > > > > > >As has been said numerous times, it is utterly pointless to define a mapping > >like this > >- you get mary-is-mary for free just by saying peer. It certainly is but he probably did it just to check whether anything changes when another code path is run (the mapping) while the previously-working result (= successful login) should not change (mary is still mary). despite the additional code path. It seems to be a way of bisecting in order to verify/falsify assumptions in his mental model. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
Am Sat, Oct 29, 2022 at 08:20:50PM -0700 schrieb Bryn Llewellyn:
> For the purpose of the tests that follow, I set up the O/S users "bob" and
> "mary" so that "id bob mary postgres" shows this:
>
> id=1002(bob) gid=1001(postgres) groups=1001(postgres)
> uid=1003(mary) gid=1001(postgres) groups=1001(postgres)
> uid=1001(postgres) gid=1001(postgres)
> groups=1001(postgres),27(sudo),114(ssl-cert)
>
> And I set up the cluster-roles "bob" and "mary" so that "\du" shows this:
>
> bob || {}
> mary || {}
> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Just a hint: you may want to use "mary_os" and "mary_db",
respectively, such that error messages can become less
ambivalent... (same for bob*).
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: How to store "blobs" efficiently for small and large sizes, with random access
> I don't know what exactly they are, but I suspect that they are just > files (segments?) in Oracle's "file system" (tablespaces/datafiles). > So pretty much what we recommend. Maybe so, but if those large segments are presented "seamlessly" in the form of a table integrated with PGs access/security infrastructure that would be really helpful for some scenarios. A view-on-top-of-file_fdw kind of thing ? LO seems to nearly be there by now, or am I misunderstanding ? Karsten
Aw: Re: ('dog$house' = quote_ident('dog$house')) is surprisingly FALSE
What we deal with in our ordinary professional work is SQL texts, program source texts, within these, SQL identifier texts, and then the conventional display of the results of SQL and program execution. To emphasize the point about resulst display, try "\d s.*" in "\t off" mode. You'll see this: Table "s.silly name" Column | Type | Collation | Nullable | Default +-+---+--+- n | integer | | | But this SQL text: drop table "s.silly name"; tells me that there's no such table. And, indeed, there isn't. Perhaps there's a table s."silly name". It is accidental if unfortunate that the is quoted with ""'s in the \d output... Karsten
Aw: Re: I slipped up so that no existing role allows connection. Is rescue possible?
> Yes, after Tom's hint, a search for "single-user" took me to that page. But, > beginner as I am, > I didn't know that single-user mode was the thing that I needed. I need a > remedial class. > Something like "PostgreSQL for those whose mental model has been conditioned > by decades of working with Oracle Database". I think it's normal to not know the Ins and Outs of a ... new software. And that's the point why that class should be an easy one: Drop the idea that PG works like Oracle 101. Then, read the manual, back to cover. Yes, one will forget most of what's written there. However, a coarse structure of a new mental model will form. Karsten
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
Am Wed, Sep 14, 2022 at 10:10:30AM +0200 schrieb Karsten Hilbert: > Am Tue, Sep 13, 2022 at 05:10:58PM -0400 schrieb Tom Lane: > > > (I recall that somewhere we have some code that warns about no-op > > grants. I wonder if issuing a warning for no-op revokes would be > > helpful.) > > Surely, in the light of security a no-op revoke is > potentially more dangerous than a no-op grant. In the sense where no-op means "despite being revoked it is still granted by another grant" rather than "the revoke is a no-op because it is already revoked", that is. (although the latter can be used to inform on the first if the latter extends to all "levels" of revokage ... :-) Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?
Am Tue, Sep 13, 2022 at 05:10:58PM -0400 schrieb Tom Lane: > (I recall that somewhere we have some code that warns about no-op > grants. I wonder if issuing a warning for no-op revokes would be > helpful.) Surely, in the light of security a no-op revoke is potentially more dangerous than a no-op grant. Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: toast useless
Gesendet: Dienstag, 13. September 2022 um 19:13 Uhr Von: "Ron" An: pgsql-general@lists.postgresql.org Betreff: Re: toast useless On 9/13/22 08:18, Simon Riggs wrote: On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro wrote: What problem do they cause you? They don't cause any problem, I was just trying to get my database as clean as possible. I have some thousands of these toast tables with 8k bytes, so I was trying to eliminate them But if there is no way, ok They might be optimized away one day, but for now, they are essential. > Why are they essential? Essential to proper operation of the database code as of now. Best, Karsten
Aw: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
I'll be happy to make a smaller example. It will, however, need to create users, a database, schemas, and some number of triggers. Because the triggers, their functions, and everything else about them follow a pattern, I can use "format()" and dynamic SQL to generate them. I'll still need those three "security definer" procedures to make the table changes that I explained. And the code to call these procedures to implement the test. So the result won't be exactly small. But, while I'm generating the triggers, I may just as well generate all eight. After all, how would I know which of the eight to skip while I don't know the intended rules for the current_role? = You'd certainly start out with all eight but then whittle down to what still exhibits the problem and post that. = Karsten
Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence
Am Wed, Jul 20, 2022 at 09:15:29AM + schrieb Sebastien Flaesch: > Thomas, we already have a similar solution. > The idea is to use the native PostgreSQL SERIAL type. Which does not guarantuee gaplessness. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: lifetime of the old CTID
> Said this, we can end this thread. Re-think the data model is not an > option. Why not ? - add a primary key to each table, say db01buch.pk - rename tables, say db01buch -> db01buch__real_table - add views, say db01buch over db1buch__real_table with "pk AS ctid" (untested, however) Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: lifetime of the old CTID
Am Wed, Jul 06, 2022 at 08:41:58AM +0200 schrieb Laurenz Albe: > Using the primary key is the proper solution. To be safe from concurrent > modifications, use a logic like in this pseudo-code: > > FOR b IN SELECT pk, other_columns FROM books WHERE condition >UPDATE books SET ... WHERE pk = ... AND condition > > Checking the condition again on the inner UPDATE will detect concurrent > data modifications. If the UPDATE changes nothing, then a book has been > removed or updated by a concurrent transaction, and you ignore it. UPDATEing conditional on XMIN not having changed might be another solution for detecting concurrent transacations if one is bent on using system columns for that. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: lifetime of the old CTID
Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz: > > On first glance, it appears that you are using the ctid as a primary key > > for a row, and that's highly not-recommended. The ctid is never intended > > to be stable in the database, as you have discovered. There are really no > > particular guarantees about ctid values being retained. > > > > I'd suggest having a proper primary key column on the table, and using that > > instead. > > Ofc, each table has its own primary key(s), used for example for the > SELECT ctid, * FROM d01buch WHERE ... > > As I said, we came to PostgreSQL from Sybase (and Oracle) and Sybase has > for each table a so called SYB_IDENTITY_COLUMN which is static for the > table and its value does not change. When we would add now to some 400 tables > an > additional INTEGER column (and triggers to fill this on INSERT) this > would be a big change in our DB layer and migration of databases in the > field. Your suggesting (thanks for it in any case) is not that easy to > implement, and no option at the moment. Christopher suggested to *use* the primary key, not to *add* one. You said that there *is* a primary key. So, more thought/explanation would need to go into why that cannot be used. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: How to set password in psql -h -d -U command line?
> I tried various ways to set password in psql command line, but got no luck. Have you tried all the methods that you showed in your mail or did you try others as well ? Best regards, Karsten
Re: PG14: "is of" vs pg_typeof
Am Sat, Apr 23, 2022 at 01:43:52PM -0700 schrieb Adrian Klaver:
> > select pg_typeof('a'::text) = any(ARRAY[pg_typeof(''::text),
> > pg_typeof(''::name)]);
> >
> >Is there anything obvious I am missing for easily
> >resurrecting the above "is of" use ?
>
> Actually it can be done as:
>
> select pg_typeof('test'::text) in ('text'::regtype, 'varchar'::regtype);
Found that, but thanks anyway.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: PG14: "is of" vs pg_typeof
Am Sat, Apr 23, 2022 at 10:14:03PM +0200 schrieb Karsten Hilbert: > I can't find anything in the changelog saying that "is of" > was removed. For what it's worth, nothing in the docs ever > said it existed either (though it did, as per real life). Oh, wait, https://www.postgresql.org/message-id/1129826.1605805...@sss.pgh.pa.us is that it ? It is gone ? Alright, alright, https://www.postgresql.org/message-id/1052846.1605802...@sss.pgh.pa.us I am rewriting my code already. Interesting how one discovers the proper search strategy only eventually, *after* asking for help. Anyway, so there, "IS OF" (the old PG one, at any rate) is gone. Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
PG14: "is of" vs pg_typeof
PostgreSQL 14.2-1
Dear readers,
it used to be possible to say (inside plpgsql):
if _value is of (text, char, varchar, name) then
val_type := ''string'';
elsif _value is of (smallint, integer, bigint, numeric, boolean) then
val_type := ''numeric'';
elsif _value is of (bytea) then
val_type := ''data'';
elsif _value is of (text[]) then
val_type := ''str_array'';
else
raise exception ''cfg.set_option(text, any, text, text, text):
invalid type of value'';
end if;
where _value is some typed value.
As of now (not sure when that started) PG 14 is giving me
gnumed_v23=> select 'a'::text is of (text, integer);
ERROR: syntax error at or near "of"
(same thing inside plpgsql)
where the log says
2022-04-23 19:58:33 GMT ERROR: 42601: syntax error at or near "of" at
character 21
2022-04-23 19:58:33 GMT LOCATION: scanner_yyerror, scan.l:1176
2022-04-23 19:58:33 GMT STATEMENT: select 'a'::text is of (text,
integer);
I can't find anything in the changelog saying that "is of"
was removed. For what it's worth, nothing in the docs ever
said it existed either (though it did, as per real life).
Now, pg_typeof is an alternative but that only goes so far:
it requires laboriously constructing an array on the right
hand side for the above use case, along the lines of:
select pg_typeof('a'::text) = any(ARRAY[pg_typeof(''::text),
pg_typeof(''::name)]);
Is there anything obvious I am missing for easily
resurrecting the above "is of" use ?
Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Transaction and SQL errors
Am Mon, Apr 04, 2022 at 11:33:14AM + schrieb Sebastien Flaesch: > Is there any plan to have an equivalent of psql's > > set ON_ERROR_ROLLBACK on > > in the DB engine? That is already what happens. SQL fails, transaction rolls back. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: How long does iteration over 4-5 million rows usually take?
> > On Apr 1, 2022, at 10:18 PM, Ron wrote: > > > > On 4/1/22 20:34, Shaozhong SHI wrote: > >> > >> I have a script running to iterate over 4-5 million rows. It keeps > >> showing up in red in PgAdmin. It remains active. > >> > >> How long does iteration over 4-5 million rows usually take? > > 4-5 million times as long as it takes to do one iteration ( if you’re doing > it correctly) I may not take quite that long because setup/teardown times might not be needed for each iteration. Best, Karsten
Aw: Additional accessors via the Extension API ?
> Suppose I have defined an additional type in a PG extension. > > Is it possible to add custom accessors to that type -much like jsonb does- > but use an API/hook without touching the core PG grammar & parser? > > Hypothetical Examples: > > Assuming I have a TextFile type I’d like to implement syntax like: > > (‘/home/me/a.txt’::TextFile).firstline > (‘/home/me/a.txt’::TextFile).lastline > (‘/home/me/a.txt’::TextFile).countlines() > (‘/home/me/a.txt’::TextFile).size() > (‘/home/me/a.txt’::TextFile).datemodified() Off on a tangent but would file_fdw help in any way ? Karsten
Re: Is there a way to automatically scan a table and determine the format of data
Am Wed, Feb 16, 2022 at 01:27:56AM + schrieb Shaozhong SHI: > Is there a way to automatically scan a table and report the format of data > for each column? pg_class But you may want to rethink the approach given that you use Python. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Operator % and its meaning and use
Dear David, > Can anyone remind me of the meaning and use of operator %. I can gladly report that I remember having seen relevant documentation on that operator while Reading up in The Fine Manual on json_to_row following the hint Ion kindly provided. It was amazing ! Reading up on that helped my understanding ! Best regards, Karsten
Re: Proposed German Translation of Code of Conduct Policy
Am Thu, Feb 10, 2022 at 03:24:54PM +0500 schrieb Umair Shahid: > > What happens if all members of the committee are impeached at once ? > > > > That is one reason to strive for diversity in the CoC Committee - the > chances of this happening are reduced to near-zero. It may be near-zero for impeachment based on sound reasons but it may still happen malevolently. However, not all cases can, or need, be accounted for explicitely. > In the unlikely event that it does happen, however, one option could be for > the Core team to ask for volunteers from the committers group (senior & > respected members of the community) to pitch in. > > If you feel this point needs to be addressed in the policy, I would > recommend starting off a new thread with your suggested amendments to the > CoC policy. Perhaps such might be contained in an ancillary document "Suggestions for execution of the Policy" containing such advice as you gave above. Other than that one might leave things as they are. > Thank you for your suggestions. These have been incorporated in the text Thanks, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Can we go beyond the standard to make Postgres radically better?
Am Wed, Feb 09, 2022 at 09:14:39PM -0800 schrieb Guyren Howe: > There are huge developer benefits available to focusing > more on making a great relational programming environment, > well outside the SQL standard. There's a seemingly small but conceptually rather significant difference between going _beyond_ a standard and being _well outside_ said standard. Which do you propose ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Counting the number of repeated phrases in a column
Am Tue, Feb 01, 2022 at 11:29:50PM + schrieb Shaozhong SHI: > How about knock unique words into discrete joint up strings? Then check > whether there is any repeated words? Does it work when you try ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Counting the number of repeated phrases in a column
Am Wed, Jan 26, 2022 at 08:35:06PM + schrieb Shaozhong SHI: > Whatever. Can we try to build a regex for 'The City of London London > Great London UK ' ? Would you be so kind as do be more specific about that "we" ? Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: Counting the number of repeated phrases in a column
> How about split up the value into individual words and keep their orders? > add words up to form individual phrase and ensure that each phrase only > consists unique/distinct words > count repeated phrases afterward > > How about this? Sure, if that serves your purpose ? So far, we (I?) can't tell because you have yet to (computably) define "phrase". Which may or may not solve the previous dilemma. (Top-posting is not liked on this list, to my knowledge.) Best, Karsten
Aw: Counting the number of repeated phrases in a column
> There is a short of a function in the standard Postgres to do the following: > > it is easy to count the number of occurrence of words, but it is rather > difficult to count the number of occurrence of phrases. > > For instance: > > A cell of value: 'Hello World' means 1 occurrence a phrase. > > A cell of value: 'Hello World World Hello' means no occurrence of any > repeated phrase. > > But, A cell of value: 'Hello World World Hello Hello World' means 2 > occurrences of 'Hello World'. > > 'The City of London, London' also has no occurrences of any repeated phrase. > > Anyone has got such a function to check out the number of occurrence of any > repeated phrases? For that to become answerable you may want to define what to do when facing ambiguity. Best, Karsten
Re: Proposed German Translation of Code of Conduct Policy
Ein Vorschlag: > Inklusivität und angemessenes Verhalten > > Das PostgreSQL-Projekt steht jedem offen, der Interesse an > der Arbeit mit PostgreSQL hat, unabhängig von seinem > Erfahrungsstand mit der Software oder mit Technologie im > Allgemeinen. Wir fördern die Entwicklung und Beiträge aller > Benutzer, unabhängig ihres Hintergrunds. Entweder "unabhängig von ihrem" oder besser "ungeachtet ihres". > Im Falle einer Beschwerde von oder gegen ein Mitglied des > CoC-Komitee oder des Core-Teams erfolgt die > Beschwerdebearbeitung wie gewohnt, mit der Ausnahme, dass die > Person, die in die Beschwerde involviert ist, in ihrer > Funktion im Komitee oder dem Core-Team vom Verfahren > ausgeschlossen wird. What happens if all members of the committee are impeached at once ? > Die Komitee kann feststellen, dass ein Verstoß gegen diese "*Das* Komitee" Regards, Karsten Hilbert -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: a very naive question about table names in Postgres
Am Fri, Dec 31, 2021 at 08:27:59PM + schrieb Martin Mueller: > I much prefer Postgres to Mysql for a variety of reasons, > but mostly for its elegant string functions. But in Mysql it > seems to be much easier to keep track of tables. May I ask for the context of "keep track of tables" ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: How to allow null as an option when using regexp_matches?
Am Wed, Dec 08, 2021 at 12:07:13PM + schrieb Shaozhong SHI:
> We can do this:
> select count(*) from regexp_matches('Great London', 'Great
> London|Information Centre|Department for Transport', 'g');
>
> Is it possible to allow null as an option? something like this
> select count(*) from regexp_matches('Great London', 'null|Great
> London|Information Centre|Department for Transport', 'g');
You seem to want to apply coalesce() judiciously.
Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: check scripts after database code change
Am Thu, Nov 18, 2021 at 05:45:37PM +0300 schrieb Dennis: > Are there any scripts that we can check after the changes we made in the > database code? > In summary, are there any automatic post-development scripts before they > come to the beta stage? What do you want those script to do ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Fault with initcap
Am Tue, Oct 12, 2021 at 09:50:16PM +0100 schrieb Shaozhong SHI: > There must be a way to do the following. > > [...] Only the first letter of each word should be capitalised. Indeed, there is. It is called "human brain in cultural context". "AI" is close nowadays, but, hopefully, not quite there yet. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: Re: Fault with initcap
> Only the first letter of each word to be capitalised/uppercased. The next step is to not top-post. Then to keep the list involved if you wish further help. Then, if you are intent on using regular expressions, look at the PostgreSQL docs for regexp_replace. Karsten
Aw: Re: Fault with initcap
Hi David, >Expected are as follows: >Notemachine >Sainsbury's bank. Now, step two: generalize that exemplary definition. Karsten
Re: PostgreSQL CHECK Constraint
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B -- Karsten Sure, they are just a search engine's use away. > Are there any examples on the web these days? > That sounds brilliant. :Am Sun, Oct 03, 2021 at 07:44:41PM +0100 schrieb Shaozhong SHI
Re: PostgreSQL CHECK Constraint
Am Sun, Oct 03, 2021 at 07:16:32PM +0100 schrieb Shaozhong SHI: > That is interesting. Can errors be captured and saved as data with > scripting? Depends on what the script does. If the script runs (or is written in) Python the canonical PG driver (psycopg2/3) will give you such data. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: pg_dump save command in output
Am Sun, Oct 03, 2021 at 06:15:54PM +0100 schrieb Joao Miguel Ferreira: > I just wanted to save the command inside the SQL file to be able to > "remember" it later. ideally I would open the file on some text editor and > see the original pg_dump command on the few first lines, as a comment > > -- original command was: psql -h some_host -p . > > > > > Are you dumping in plain or custom format? > > > > plain (SQL) Run the dump from a bash script which pre-catenates (Is that even a word ? it is now :) the command into the plain (SQL) output after the fact. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: PostgreSQL - Ordering Table based of Foreign Key
Am Sun, Oct 03, 2021 at 08:48:13AM +0100 schrieb FOUTE K. Jaurès: > I want to order tables based on the foreign key so that I can delete tables > one by one without facing "ERROR: update or delete on table "table" > violates foreign key constraint. DETAIL: Key is still referenced from table" drop table if exists ... cascade; Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Timestamp with vs without time zone.
Am Wed, Sep 22, 2021 at 08:11:50PM +1200 schrieb Tim Uckun: > A korean user will fetch you single page app as static HTML from S3 > with cloudfront. It will hit your japanese API server, which will > fetch the data from your japanese read only replica with the master > being in Australia. > > The master DB writes the records has to know your end user is in > Korea somehow so you have to carry that time zone all the way across > those tiers. One does not: as soon as the real-world concept of point-in-time hits the machine it gets converted to UTC. When it leaves the machine (towards user consumption) it gets converted to whatever is desired. UTC = UTF8 Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Aw: Re: Re: Timestamp with vs without time zone.
> It's just that the phrase > "timestamp with time zone" would seem to indicate the time zone is > stored somewhere in there. Now, I can fully agree with _that_ :-) Karsten
Aw: Re: Re: Timestamp with vs without time zone.
> > > It seems like it would be so much more useful if the timestamp with > > > time zone type actually stored the time zone in the record. > > > > Which one ? > > > One specified by the user. Many date formats carry either an offset > or the time zone information. What would that TZ mean, exactly, especially in relation to the timestamp itself ? Would the timestamp be stored as that TZ ? Karsten
Aw: Re: Timestamp with vs without time zone.
> > It seems like it would be so much more useful if the timestamp with > > time zone type actually stored the time zone in the record. > > This has been requested before, and it would be closer to the intention > of the SQL standard, but I guess it won't happen. > > For one, it would change on-disk storage, which would make it > impossible to use pg_upgrade. It also would require timestamps to > occupy more than 8 bytes. > > The best solution is probably to explicitly store the time zone as > an additional column. Would that not be a perfect candidate for a fully fleshed out, exemplary composite type ? (but, then, yes it would beg the question what the TZ field of the composite is to actually mean...) Karsten
Aw: Re: Timestamp with vs without time zone.
> It seems like it would be so much more useful if the timestamp with > time zone type actually stored the time zone in the record. Which one ? Karsten
Re: Issue with a query while running on a remote host
Deep packet inspection naively scanning for potential
fragments of bash scripts being transferred ?
Karsten
Am Fri, Aug 27, 2021 at 12:32:09PM +0530 schrieb Ninad Shah:
> Date: Fri, 27 Aug 2021 12:32:09 +0530
> From: Ninad Shah
> To: pgsql-general
> Subject: Issue with a query while running on a remote host
>
> Hello All,
>
> While working with a PostgreSQL database, I came across an issue where data
> is not being fetched over the network.
>
> Version : PostgreSQL 11.10
> Operating system : RHEL 8.4
>
> *Issue description:*
>
> We tried to execute the below query on the database host using psql prompt,
> it works without any issue.
>
> select regexp_replace(state_cd, E'[\\n\\r]+', '', 'g' ) as state_cd,
> off_cd, user_cd, regexp_replace(user_name, E'[\\n\\r]+', '', 'g' ) as
> user_name, regexp_replace(desig_cd, E'[\\n\\r]+', '', 'g' ) as desig_cd,
> regexp_replace(user_id, E'[\\n\\r]+', '', 'g' ) as user_id,
> regexp_replace(user_pwd, E'[\\n\\r]+', '', 'g' ) as user_pwd,
> regexp_replace(phone_off, E'[\\n\\r]+', '', 'g' ) as phone_off, mobile_no,
> regexp_replace(email_id, E'[\\n\\r]+', '', 'g' ) as email_id,
> regexp_replace(user_catg, E'[\\n\\r]+', '', 'g' ) as user_catg,
> regexp_replace(status, E'[\\n\\r]+', '', 'g' ) as status, created_by,
> created_dt, aadhaar, op_dt, regexp_replace(login_ipaddress, E'[\\n\\r]+',
> '', 'g' ) as login_ipaddress, regexp_replace(forget_password, E'[\\n\\r]+',
> '', 'g' ) as forget_password, regexp_replace(newuser_change_password,
> E'[\\n\\r]+', '', 'g' ) as newuser_change_password from tm_user_info
> where ( user_cd >= 1068540014 ) AND ( user_cd <= 2137079303 ) ;
>
> While trying to execute the same query over the network using psql prompt,
> the execution doesn't finish.
>
> *My Analysis:*
>
> By digging further, we came to see that a specific record was causing the
> issue, and by further analysis, we saw that the records that contain a
> specific string("*bash@*") in the column user_id are not being fetched over
> the network.
>
> To confirm that, we also changed some records manually by creating a test
> table. And, we were able to reproduce the issue.
>
> vow4_xdb=# select * from vahan4.test_tbl where user_cd =2106011301;
> .
> .
>
> But, this issue doesn't occur if we try to fetch on the database host or
> via PgAdmin4. In such cases, we get the record in a few milliseconds.
>
> *Surprisingly, this table has only one record.*
>
> There is no table/row-level lock found here.
>
>
> *Table definition:-*
> Table "test_tbl"
> Column |Type | Collation |
> Nullable | Default | Storage | Stats targe
> t | Description
> -+-+---+--+-+--+
> --+-
> state_cd| character varying(2)| | not
> null | | extended |
> |
> off_cd | numeric(5,0)| | not
> null | | main |
> |
> user_cd | numeric(10,0) | | not
> null | | main |
> |
> user_name | character varying(99) | | not
> null | | extended |
> |
> desig_cd| character varying(10) | | not
> null | | extended |
> |
> user_id | character varying(20) | | not
> null | | extended |
> |
> user_pwd| character varying(100) | | not
> null | | extended |
> |
> phone_off | character varying(20) | |
> | | extended |
> |
> mobile_no | numeric(10,0) | | not
> null | | main |
> |
> email_id| character varying(50) | |
> | | extended |
> |
> user_catg | character varying(1)| | not
> null | | extended |
> |
> status | character varying(1)| | not
> null | | extended |
> |
> created_by | numeric(10,0) | | not
> null | | main |
> |
> created_dt | date| | not
> null | | plain|
> |
> aadhaar | numeric(12,0) | |
> | | main |
> |
> op_dt | timestamp without time zone | | not
> null | now() | plain|
> |
> login_ipaddress | character varying(20) | |
> | | extended |
> |
> forget_password | character varying(1)| |
> | | extended |
> |
> newuser_change_password | character varying(1)| |
> | | extended |
> |
> Indexes:
> "tm_user_info_pkey" PRIMARY KEY, btree (user_cd)
>
Re: PostgreSQL reference coffee mug
Am Fri, Aug 06, 2021 at 08:09:03PM +0200 schrieb Matthias Apitz: > The prototype is ready. Nice. Now the elephant needs to fade into the background. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
