Re: Transaction issue

2024-06-20 Thread Karsten Hilbert
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

2024-06-18 Thread Karsten Hilbert
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

2024-06-18 Thread Karsten Hilbert
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

2024-06-16 Thread Karsten Hilbert
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

2024-06-14 Thread Karsten Hilbert
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

2024-06-12 Thread Karsten Hilbert
> > 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

2024-06-11 Thread Karsten Hilbert
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 ?

2024-03-07 Thread Karsten Hilbert
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 ?

2024-03-07 Thread Karsten Hilbert
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 ?

2024-02-17 Thread Karsten Hilbert
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 ?

2024-02-17 Thread Karsten Hilbert
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 ?

2024-02-17 Thread Karsten Hilbert
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 ?

2024-02-17 Thread Karsten Hilbert
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 ?

2024-02-17 Thread Karsten Hilbert
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

2024-02-12 Thread Karsten Hilbert
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

2024-02-11 Thread Karsten Hilbert
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

2024-01-16 Thread Karsten Hilbert
> 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?

2024-01-13 Thread Karsten Hilbert
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()

2024-01-11 Thread Karsten Hilbert
> > 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()

2024-01-11 Thread Karsten Hilbert
> 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

2023-08-25 Thread Karsten Hilbert
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

2023-08-17 Thread Karsten Hilbert
 
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

2023-08-04 Thread Karsten Hilbert
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

2023-08-03 Thread Karsten Hilbert
> > 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

2023-08-03 Thread Karsten Hilbert
> 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

2023-08-03 Thread Karsten Hilbert
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?

2023-06-27 Thread Karsten Hilbert
> 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

2023-06-15 Thread Karsten Hilbert
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

2023-06-14 Thread Karsten Hilbert
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

2023-04-14 Thread Karsten Hilbert
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

2023-04-10 Thread Karsten Hilbert
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

2023-04-10 Thread Karsten Hilbert
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?

2023-02-21 Thread Karsten Hilbert
> >> 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

2023-01-05 Thread Karsten Hilbert
> 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

2022-12-21 Thread Karsten Hilbert
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

2022-12-21 Thread Karsten Hilbert
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

2022-12-21 Thread Karsten Hilbert
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

2022-12-05 Thread Karsten Hilbert
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

2022-12-04 Thread Karsten Hilbert
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

2022-12-04 Thread Karsten Hilbert
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

2022-12-04 Thread Karsten Hilbert
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

2022-12-04 Thread Karsten Hilbert
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

2022-12-04 Thread Karsten Hilbert
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

2022-12-04 Thread Karsten Hilbert
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

2022-12-03 Thread Karsten Hilbert
> 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...

2022-11-23 Thread Karsten Hilbert
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

2022-11-14 Thread Karsten Hilbert
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

2022-11-13 Thread Karsten Hilbert
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

2022-11-13 Thread Karsten Hilbert
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

2022-11-10 Thread Karsten Hilbert
> > 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

2022-11-10 Thread Karsten Hilbert
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

2022-11-09 Thread Karsten Hilbert
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

2022-11-08 Thread Karsten Hilbert
> 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"

2022-11-01 Thread Karsten Hilbert
> 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

2022-10-30 Thread Karsten Hilbert
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

2022-10-30 Thread Karsten Hilbert
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

2022-10-20 Thread Karsten Hilbert
> 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

2022-10-06 Thread Karsten Hilbert


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?

2022-09-20 Thread Karsten Hilbert
> 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?

2022-09-14 Thread Karsten Hilbert
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?

2022-09-14 Thread 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.

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B




Aw: Re: toast useless

2022-09-13 Thread Karsten Hilbert

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

2022-08-11 Thread Karsten Hilbert
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

2022-07-20 Thread Karsten Hilbert
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

2022-07-06 Thread Karsten Hilbert
> 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

2022-07-06 Thread Karsten Hilbert
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

2022-07-06 Thread Karsten Hilbert
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?

2022-04-28 Thread Karsten Hilbert
> 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

2022-04-23 Thread Karsten Hilbert
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

2022-04-23 Thread Karsten Hilbert
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

2022-04-23 Thread Karsten Hilbert
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

2022-04-04 Thread Karsten Hilbert
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?

2022-04-02 Thread Karsten Hilbert
> > 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 ?

2022-02-20 Thread Karsten Hilbert
> 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

2022-02-16 Thread Karsten Hilbert
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

2022-02-15 Thread Karsten Hilbert
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

2022-02-10 Thread Karsten Hilbert
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?

2022-02-10 Thread Karsten Hilbert
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

2022-02-01 Thread Karsten Hilbert
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

2022-01-26 Thread Karsten Hilbert
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

2022-01-25 Thread Karsten Hilbert
> 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

2022-01-25 Thread Karsten Hilbert
> 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

2022-01-24 Thread Karsten Hilbert
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

2021-12-31 Thread Karsten Hilbert
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?

2021-12-08 Thread Karsten Hilbert
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

2021-11-18 Thread Karsten Hilbert
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

2021-10-12 Thread Karsten Hilbert
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

2021-10-12 Thread Karsten Hilbert
> 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

2021-10-12 Thread Karsten Hilbert
Hi David,

>Expected are as follows:
>Notemachine
>Sainsbury's bank.

Now, step two: generalize that exemplary definition.

Karsten





Re: PostgreSQL CHECK Constraint

2021-10-03 Thread Karsten Hilbert
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

2021-10-03 Thread Karsten Hilbert
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

2021-10-03 Thread Karsten Hilbert
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

2021-10-03 Thread Karsten Hilbert
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.

2021-09-22 Thread Karsten Hilbert
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.

2021-09-21 Thread Karsten Hilbert
> 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.

2021-09-21 Thread Karsten Hilbert
> > > 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.

2021-09-21 Thread Karsten Hilbert
> > 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.

2021-09-21 Thread Karsten Hilbert
> 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

2021-08-27 Thread Karsten Hilbert
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

2021-08-07 Thread Karsten Hilbert
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




  1   2   >