Re: Description field for tables and views
> On Jul 3, 2024, at 13:24, Kent Dorfman wrote: > Is it SQL standard or postgres specific? It's not in the SQL standard (at the bottom of each page for each SQL command is a note regarding its relationship with the SQL standard). Other DBMS implement something similar, however.
Re: Description field for tables and views
On 7/3/24 15:30, Christophe Pettus wrote: On Jul 3, 2024, at 12:28, Kent Dorfman wrote: Is there any psql function/command to add a description field to a table or view definition in the system? Allow me to introduce you to my good friend "COMMENT": https://www.postgresql.org/docs/current/sql-comment.html Thanks for the hint! It's the simplest and most direct solution. Been typing SQL for 40 years and haven't had occasion to use COMMENT. Is it SQL standard or postgres specific? Am not a DBA by trade but an embedded systems guy.
Re: Description field for tables and views
Στις 3/7/24 22:28, ο/η Kent Dorfman έγραψε: I think I already know the answer but asking here is probably quicker turnaround than researching it. I've gotten into a "view bloat" scenario with many many custom views that I cannot remember what they actually do. Is there any psql function/command to add a description field to a table or view definition in the system? Would be nice to have a one line general text note capability to quickly see what a complicated view does when looking at the defintion via # \dv+ Looks like the command has the field present, but not sure how to populate it. am using PostgreSQL 13.xx in Debian. In addition to the COMMENT advised by PostgreSQL friends above, you might put your schema into some version control system, such as git, so you can have all the info : why something was added/changed, by whom, what for, etc. -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
Can't dump new-style sequences independently from their tables.
Quick example:
xof=# CREATE TABLE t1 (id SERIAL PRIMARY KEY);
CREATE TABLE
xof=# CREATE TABLE t2 (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY);
CREATE TABLE
xof=# \d+
List of relations
Schema | Name| Type | Owner | Persistence | Access method |Size
| Description
+---+--+---+-+---++-
public | t1| table| xof | permanent | heap | 0 bytes
|
public | t1_id_seq | sequence | xof | permanent | | 8192
bytes |
public | t2| table| xof | permanent | heap | 0 bytes
|
public | t2_id_seq | sequence | xof | permanent | | 8192
bytes |
(4 rows)
Swift:~ xof$ pg_dump -t 't1_id_seq' | fgrep 'setval'
SELECT pg_catalog.setval('public.t1_id_seq', 1, false);
Swift:~ xof$ pg_dump -t 't2_id_seq' | fgrep 'setval'
Swift:~ xof$ pg_dump -t 't2' | fgrep 'setval'
SELECT pg_catalog.setval('public.t2_id_seq', 1, false);
Swift:~ xof$
So, you can dump a sequence created with SERIAL independently from the table it
is owned by, but not a sequence created by GENERATED ALWAYS AS IDENTITY; you
need to dump the owning table. It's easily worked around, but I'm curious why
that is.
Re: Description field for tables and views
Comment is probably what you’re looking for. If you don’t want to use that: it’s a database. Make a table. Put whatever information in there that you need. Either look up by view name, or schema + view name, or oid: https://www.postgresql.org/docs/current/datatype-oid.html > On Jul 3, 2024, at 12:28, Kent Dorfman wrote: > > I think I already know the answer but asking here is probably quicker > turnaround than researching it. > > I've gotten into a "view bloat" scenario with many many custom views that I > cannot remember what they actually do. Is there any psql function/command to > add a description field to a table or view definition in the system? Would > be nice to have a one line general text note capability to quickly see what a > complicated view does when looking at the defintion via > # \dv+ > > Looks like the command has the field present, but not sure how to populate it. > > am using PostgreSQL 13.xx in Debian. > >
Re: Description field for tables and views
> On Jul 3, 2024, at 12:28, Kent Dorfman wrote: > > Is there any psql function/command to add a description field to a table or > view definition in the system? Allow me to introduce you to my good friend "COMMENT": https://www.postgresql.org/docs/current/sql-comment.html
Description field for tables and views
I think I already know the answer but asking here is probably quicker turnaround than researching it. I've gotten into a "view bloat" scenario with many many custom views that I cannot remember what they actually do. Is there any psql function/command to add a description field to a table or view definition in the system? Would be nice to have a one line general text note capability to quickly see what a complicated view does when looking at the defintion via # \dv+ Looks like the command has the field present, but not sure how to populate it. am using PostgreSQL 13.xx in Debian.
Query 2 Node HA test case result
Hello everyone, We are doing a POC on postgres HA setup with streaming replication (async) using pgpool-II as a load balancing & connection pooling and repmgr for setting up HA & automatic failover. We are applying a test case, like isolating the VM1 node from the Network completely for more than 2 mins and again plug-in back the network, since we want to verify how the system works during network glitches, any chances of split-brain or so. Our current setup looks like below, 2 VM's on Azure cloud, each VM has Postgres running along with Pgpool service. [image: image.png] We enabled watchdog and assigned a delegate IP *NOTE: as per some limitations we are using a floating IP and used for delegate IP.* During the test, here are our observations: 1. Client connections got hung from the time the VM1 got lost from the network and till VM1 gets back to normal. 2. Once the VM1 is lost then Pgpool promotes the VM2 as LEADER node and Postgres Standby got promoted to Primary on VM2 as well, but still client connections are not connecting to the new primary. Why is this not happening? 3. Once the VM1 is back to network, there is a split brain situation, where pgpool on VM1 takes the lead to become LEADER node (pgpool.log shows). and from then the client connects to the VM1 node via VIP. *pgpool.conf * sr_check_period 10sec health_check_period 30sec health_check_timeout 20 sec health_check_max_retries 3 health_check_retry_delay 1 wd_lifecheck_method = 'heartbeat' wd_interval = 10 wd_heartbeat_keepalive = 2 wd_heartbeat_deadtime = 30 *Logs information: * >From VM2: Pgpool.log 14:30:17 N/w disconnected After 10 sec the streaming replication check failed and got timed out. 2024-07-03 14:30:26.176: sr_check_worker pid 58187: LOG: failed to connect to PostgreSQL server on "staging-ha0001:5432", timed out Then pgpool failed to do health check since it got timed out as per health_check_timeout set to 20 sec 2024-07-03 14:30:35.869: health_check0 pid 58188: LOG: failed to connect to PostgreSQL server on "staging-ha0001:5432", timed out Re-trying health_check & sr_check but again timed out. 2024-07-03 14:30:46.187: sr_check_worker pid 58187: LOG: failed to connect to PostgreSQL server on "staging-ha0001:5432", timed out 2024-07-03 14:30:46.880: health_check0 pid 58188: LOG: failed to connect to PostgreSQL server on "staging-ha0001:5432", timed out Watchdog received a message saying the Leader node is lost. 2024-07-03 14:30:47.192: watchdog pid 58151: WARNING: we have not received a beacon message from leader node "staging-ha0001: Linux staging-ha0001" 2024-07-03 14:30:47.192: watchdog pid 58151: DETAIL: requesting info message from leader node 2024-07-03 14:30:54.312: watchdog pid 58151: LOG: read from socket failed, remote end closed the connection 2024-07-03 14:30:54.312: watchdog pid 58151: LOG: client socket of staging-ha0001: Linux staging-ha0001 is closed 2024-07-03 14:30:54.313: watchdog pid 58151: LOG: remote node "staging-ha0001: Linux staging-ha0001" is reporting that it has lost us 2024-07-03 14:30:54.313: watchdog pid 58151: LOG: we are lost on the leader node "staging-ha0001: Linux staging-ha0001" Re-trying health_check & sr_check but again timed out. 2024-07-03 14:30:57.888: health_check0 pid 58188: LOG: failed to connect to PostgreSQL server on "staging-ha0001:5432", timed out 2024-07-03 14:30:57.888: health_check0 pid 58188: LOG: health check retrying on DB node: 0 (round:3) 2024-07-03 14:31:06.201: sr_check_worker pid 58187: LOG: failed to connect to PostgreSQL server on "staging-ha0001:5432", timed out After 10 sec from the time we lost the leader node, watchdog changed current node to LEADER node 2024-07-03 14:31:04.199: watchdog pid 58151: LOG: watchdog node state changed from [STANDING FOR LEADER] to [LEADER] health_check is failed on node 0 and it received a degenerated request for node 0 and the pgpool main process started quarantining staging-ha0001(5432) (shutting down) 2024-07-03 14:31:08.202: watchdog pid 58151: LOG: setting the local node "staging-ha0002: Linux staging-ha0002" as watchdog cluster leader 2024-07-03 14:31:08.202: watchdog pid 58151: LOG: signal_user1_to_parent_with_reason(1) 2024-07-03 14:31:08.202: watchdog pid 58151: LOG: I am the cluster leader node but we do not have enough nodes in cluster 2024-07-03 14:31:08.202: watchdog pid 58151: DETAIL: waiting for the quorum to start escalation process 2024-07-03 14:31:08.202: main pid 58147: LOG: Pgpool-II parent process received SIGUSR1 2024-07-03 14:31:08.202: main pid 58147: LOG: Pgpool-II parent process received watchdog state change signal from watchdog 2024-07-03 14:31:08.899: health_check0 pid 58188: LOG: failed to connect to PostgreSQL server on "staging-ha0001:5432", timed out 2024-07-03 14:31:08.899: health_check0 pid 58188: LOG: health check failed on node 0 (timeout:0) 2024-07-03 14:31:08.899: health_check0
Re: printing PGresult content with gdb
clippe...@gmx.fr writes: > I don't know if it is the right mailing list, but i was > wondering if one could introspect via gdb the content of PGresult. You might have better luck with that if you install the debuginfo RPM corresponding to your libpq RPM. PGresult's innards are not exposed to applications by libpq-fe.h, so your own app's debug data is not going to contain the details of the struct. But I think it would be available to gdb if libpq's debug symbols were installed. regards, tom lane
Re: Accommodating alternative column values [RESOLVED]
On Wed, 3 Jul 2024, Adrian Klaver wrote:
alter table array_conv alter column email type varchar[] using array[email];
select * from array_conv ;
id |email
+-
1 | {adrian.kla...@aklaver.com}
2 | {akla...@example.com}
Adrian,
Given my inexperience with arrays this seems to be the simplest of all
offered syntaxes.
Another valuable postgres lesson learned.
Much appreciated,
Rich
Re: Accommodating alternative column values
On 7/3/24 07:13, Rich Shepard wrote:
On Wed, 3 Jul 2024, David G. Johnston wrote:
Yeah, the simply cast suggested will not work. You’d have to apply an
expression that turns the current contents into an array. The current
contents are not likely to be an array literal.
David,
No, it's not now an array.
I thought that this expression would work, but it doesn't:
bustrac=# alter table people alter column email set data type
varchar(64)[] using email::varchar(64)[];
RROR: malformed array literal: "fr...@dmipx.com"
DETAIL: Array value must start with "{" or dimension information.
If I correctly understand the error detail I'd need to change the contents
of that column for all 1280 rows to enclose the contents in curly braces
before I can convert the datatype to an array. Is that correct?
An example:
create table array_conv(id integer, email varchar(64));
insert into array_conv values (1, 'adrian.kla...@aklaver.com'), (2,
'akla...@example.com');
select * from array_conv ;
id | email
+---
1 | adrian.kla...@aklaver.com
2 | akla...@example.com
alter table array_conv alter column email type varchar[] using array[email];
select * from array_conv ;
id |email
+-
1 | {adrian.kla...@aklaver.com}
2 | {akla...@example.com}
Rich
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Accommodating alternative column values
On 2024-07-03 07:13:47 -0700, Rich Shepard wrote:
> On Wed, 3 Jul 2024, David G. Johnston wrote:
> > Yeah, the simply cast suggested will not work. You’d have to apply an
> > expression that turns the current contents into an array. The current
> > contents are not likely to be an array literal.
>
> David,
>
> No, it's not now an array.
>
> I thought that this expression would work, but it doesn't:
> bustrac=# alter table people alter column email set data type varchar(64)[]
> using email::varchar(64)[];
> RROR: malformed array literal: "fr...@dmipx.com"
> DETAIL: Array value must start with "{" or dimension information.
>
> If I correctly understand the error detail I'd need to change the contents
> of that column for all 1280 rows to enclose the contents in curly braces
> before I can convert the datatype to an array. Is that correct?
No. You need *some* way of creating an array with a single element which
is your email address. Constructing a valid array literal as a text and
casting that to array type is one way to do this. However, it seems like
a rather cumbersome and error-prone way to me.
As Raymond Hettinger likes to say: "There must be a better way".
And indeed, https://www.postgresql.org/docs/current/functions-array.html
shows lots of array values written as ARRAY[1, 2, 3] or similar. So that
makes it likely that ARRAY[email] creates an array with the intended
contents.
Try it with
select array[email] from people;
If that looks promising, you can use it in an alter table statement
(Torsten already posted the solution, but I wanted to expand a bit on
how to find it).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Accommodating alternative column values
On Wed, Jul 3, 2024 at 7:13 AM Rich Shepard
wrote:
> On Wed, 3 Jul 2024, David G. Johnston wrote:
>
> > Yeah, the simply cast suggested will not work. You’d have to apply an
> > expression that turns the current contents into an array. The current
> > contents are not likely to be an array literal.
>
> David,
>
> No, it's not now an array.
>
> I thought that this expression would work, but it doesn't:
> bustrac=# alter table people alter column email set data type
> varchar(64)[] using email::varchar(64)[];
> RROR: malformed array literal: "fr...@dmipx.com"
> DETAIL: Array value must start with "{" or dimension information.
>
> If I correctly understand the error detail I'd need to change the contents
> of that column for all 1280 rows to enclose the contents in curly braces
> before I can convert the datatype to an array. Is that correct?
>
>
Assuming today there is only one email per row, no, see Torsten's reply.
You also wouldn't need to perform an update..."using ('{' || email ||
'}')::text[]" if just doing that...
David J.
Re: Accommodating alternative column values
Check this out
https://www.postgresql.org/docs/16/arrays.html#ARRAYS-INPUT
You can use
('{' || email || '}')::varchar(64)[]
or the syntax I suggested earlier.
On Wed, Jul 3, 2024 at 4:13 PM Rich Shepard
wrote:
> On Wed, 3 Jul 2024, David G. Johnston wrote:
>
> > Yeah, the simply cast suggested will not work. You’d have to apply an
> > expression that turns the current contents into an array. The current
> > contents are not likely to be an array literal.
>
> David,
>
> No, it's not now an array.
>
> I thought that this expression would work, but it doesn't:
> bustrac=# alter table people alter column email set data type
> varchar(64)[] using email::varchar(64)[];
> RROR: malformed array literal: "fr...@dmipx.com"
> DETAIL: Array value must start with "{" or dimension information.
>
> If I correctly understand the error detail I'd need to change the contents
> of that column for all 1280 rows to enclose the contents in curly braces
> before I can convert the datatype to an array. Is that correct?
>
> Rich
>
>
>
Re: Accommodating alternative column values
On Wed, 3 Jul 2024, David G. Johnston wrote:
Yeah, the simply cast suggested will not work. You’d have to apply an
expression that turns the current contents into an array. The current
contents are not likely to be an array literal.
David,
No, it's not now an array.
I thought that this expression would work, but it doesn't:
bustrac=# alter table people alter column email set data type varchar(64)[]
using email::varchar(64)[];
RROR: malformed array literal: "fr...@dmipx.com"
DETAIL: Array value must start with "{" or dimension information.
If I correctly understand the error detail I'd need to change the contents
of that column for all 1280 rows to enclose the contents in curly braces
before I can convert the datatype to an array. Is that correct?
Rich
Re: Accommodating alternative column values
The USING phrase basically answers the question how do I convert an existing value of the old type to the new type. On Wed, Jul 3, 2024 at 4:03 PM Rich Shepard wrote: > On Wed, 3 Jul 2024, Rich Shepard wrote: > > > What I've tried: > > bustrac=# alter table people alter column email set data type > varchar(64) []; > > ERROR: column "email" cannot be cast automatically to type character > > varying[] > > HINT: You might need to specify "USING email::character varying(64)[]". > > What I forgot to mention is that the current datatype is varchar(64) and I > want to make it an array. > > Rich > > >
Re: Accommodating alternative column values
On Wednesday, July 3, 2024, Rich Shepard wrote: > On Wed, 3 Jul 2024, Rich Shepard wrote: > > What I've tried: >> bustrac=# alter table people alter column email set data type varchar(64) >> []; >> ERROR: column "email" cannot be cast automatically to type character >> varying[] >> HINT: You might need to specify "USING email::character varying(64)[]". >> > > What I forgot to mention is that the current datatype is varchar(64) and I > want to make it an array. > > Yeah, the simply cast suggested will not work. You’d have to apply an expression that turns the current contents into an array. The current contents are not likely to be an array literal. David J.
Re: Accommodating alternative column values
You could try ALTER TABLE ... SET TYPE TEXT[] USING ARRAY[email]::TEXT[] something along these lines. On Wed, Jul 3, 2024 at 3:58 PM Rich Shepard wrote: > On Tue, 2 Jul 2024, Christophe Pettus wrote: > > > To be clear, I wasn't suggesting stuffing them all into a text column > with > > a delimiter, but storing them in a text *array* field, each email address > > one component of the array. > > Christophe, > > I'm not using the proper syntax and the postgres alter table doc has no > example in the alter column choices. > > What I've tried: > bustrac=# alter table people alter column email set data type varchar(64) > []; > ERROR: column "email" cannot be cast automatically to type character > varying[] > HINT: You might need to specify "USING email::character varying(64)[]". > > How do I incorporate the "USING email::..." string? > > TIA, > > Rich > > >
Re: Accommodating alternative column values
On Wed, 3 Jul 2024, Rich Shepard wrote: What I've tried: bustrac=# alter table people alter column email set data type varchar(64) []; ERROR: column "email" cannot be cast automatically to type character varying[] HINT: You might need to specify "USING email::character varying(64)[]". What I forgot to mention is that the current datatype is varchar(64) and I want to make it an array. Rich
Re: Accommodating alternative column values
On Wednesday, July 3, 2024, Rich Shepard wrote: > > I'm not using the proper syntax and the postgres alter table doc has no > example in the alter column choices. Simpler syntax forms tend to get skipped over when doing examples. > > How do I incorporate the "USING email::..." string? > ALTER [ COLUMN ] *column_name* [ SET DATA ] TYPE *data_type* [ COLLATE *collation* ] [ USING *expression* ] David J.
Re: Accommodating alternative column values
On Tue, 2 Jul 2024, Christophe Pettus wrote: To be clear, I wasn't suggesting stuffing them all into a text column with a delimiter, but storing them in a text *array* field, each email address one component of the array. Christophe, I'm not using the proper syntax and the postgres alter table doc has no example in the alter column choices. What I've tried: bustrac=# alter table people alter column email set data type varchar(64) []; ERROR: column "email" cannot be cast automatically to type character varying[] HINT: You might need to specify "USING email::character varying(64)[]". How do I incorporate the "USING email::..." string? TIA, Rich
Re: printing PGresult content with gdb
On Tue, 2024-07-02 at 18:13 +0200, clippe...@gmx.fr wrote:
>
> Hi all
> I don't know if it is the right mailing list, but i was wondering if
> one could introspect via gdb the content of PGresult.
> In my case i got a coredump and when i tried to analyze the core and
> try to print the content of PGresult i got incomplete type
> I'm using libpq-13.3 (installed via libpq-13.3-1.el8_4.x86_64,
> RHEL8.5). Any help would be appreciated (i'm trying to get the
> errMesg value of this field)
> (gdb) p res
> $1 = (PGresult *) 0x7f0718000b80
> (gdb) p *res
> $2 =
> (gdb) ptype res
> type = struct pg_result {
>
> } *
> (gdb) explore res
> 'res' is a pointer to a value of type 'PGresult'
> Continue exploring it as a pointer to a single value [y/n]: y
> The value of '*res' is of type 'PGresult' which is a typedef of type
> 'pg_result'
> The value of '*res' is a struct/class of type 'pg_result' with no
> fields.
> (gdb)
> thanks for your hints
> doris
https://wiki.postgresql.org/wiki/Developer_FAQ#Why_do_we_use_Node_and_List_to_make_data_structures.3F
may be of help.
Instead of printing values in gdb format, you can use the next two
commands to print out List, Node, and structure contents in a verbose
format that is easier to understand. Lists are unrolled into nodes, and
nodes are printed in detail. The first prints in a short format, and
the second in a long format:
(gdb) call print(any_pointer)
(gdb) call pprint(any_pointer)
The output appears in the server log file, or on your screen if you are
running a backend directly without a postmaster.
I found that from https://wiki.postgresql.org/wiki/Developer_FAQ#gdb
printing PGresult content with gdb
Hi all
I don't know if it is the right mailing list, but i was wondering if one could introspect via gdb the content of PGresult.
In my case i got a coredump and when i tried to analyze the core and try to print the content of PGresult i got incomplete type
I'm using libpq-13.3 (installed via libpq-13.3-1.el8_4.x86_64, RHEL8.5). Any help would be appreciated (i'm trying to get the errMesg value of this field)
(gdb) p res
$1 = (PGresult *) 0x7f0718000b80
(gdb) p *res
$2 =
(gdb) ptype res
type = struct pg_result {
} *
(gdb) explore res
'res' is a pointer to a value of type 'PGresult'
Continue exploring it as a pointer to a single value [y/n]: y
The value of '*res' is of type 'PGresult' which is a typedef of type 'pg_result'
The value of '*res' is a struct/class of type 'pg_result' with no fields.
(gdb)
thanks for your hints
doris
