Hi everyone,
we created an index on a table to improve the performance for a SQL statement.
After executing “vacuum analyze mytable”, the index is used. I know that there
is also an autovacuum/autoanalyzer configured. I can see that autovacuum and
autoanalyzer ran recently. But the index is sti
On Mon, Jun 10, 2024 at 5:16 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Jun 10, 2024 at 12:43 PM Ron Johnson
>> wrote:
>
>
>> Most useful to you will be some number of "ALTER TABLE DISABLE
>> TRIGGER ALL;" statements near the beginning of the file, and their "ALTER
>> TA
Hi
On Mon, Jun 10, 2024 at 5:07 PM Torsten Förtsch
wrote:
> Hi,
>
> This is a VACUUM FREEZE process.
>
> -[ RECORD 1 ]--+--
> pid| 129471
> datid | 16401
> datname| feed
> relid | 1889166
> phase | scanning heap
>
On Mon, 2024-06-10 at 13:20 -0700, Adrian Klaver wrote:
> https://www.postgresql.org/docs/15/sql-createtrigger.html
>
> That is either a function or a procedure.
The trigger function must be a function, it cannot be a procedure.
The syntax EXECUTE PROCEDURE is just for backward compatibility with
On Monday, June 10, 2024, Rich Shepard wrote:
>
> Is there a way to reset the sequence to the maximum
> number +1? I don't recall seeing this in the postgres docs but will look
> again.
>
https://www.postgresql.org/docs/current/functions-sequence.html
setval
The bigint argument can be compute
> On Jun 10, 2024, at 18:10, Rich Shepard wrote:
> Thanks, Christophe. Is there a way to reset the sequence to the maximum
> number +1? I don't recall seeing this in the postgres docs but will look
> again.
The sequence functions are documented here:
https://www.postgresql.org/docs/cu
On Mon, 10 Jun 2024, David G. Johnston wrote:
For efficiency the only thing used to determine the next value of a
sequence is the stored value of the last sequence value issued. Where that
value may have been used, in a table as a PK or some other purpose, does
not enter into it. Using a sequenc
On Mon, 10 Jun 2024, Christophe Pettus wrote:
Strictly speaking, the sequence underlying nextval() has no idea what
primary keys are or are not in use. It's just a transaction-ignoring
counter that increases with each nextval() call. The only reason that
you'd get duplicate key errors in this ca
Thomas Munro writes:
> On Tue, Jun 11, 2024 at 2:36 AM Dominique Devienne
> wrote:
>> PQsocketPoll() being based on time_t, it has only second resolution, AFAIK.
>> Despite the [underlying implementation in fe-misc.c][2] supporting at
>> least milliseconds.
> Yeah, that is not nice and your com
> On Jun 10, 2024, at 15:57, Rich Shepard wrote:
> When I tried inserting new rows in the companies table psql told me that PK
> value 2310 already existed. Selecting max(PK) returned 2341. When entering
> multiple new rows is there a way to ignore gaps?
Strictly speaking, the sequence underly
On Mon, Jun 10, 2024 at 3:57 PM Rich Shepard
wrote:
>
> I found a web page that explains how to find the gaps in a sequence, yet I
> want to understand why nextval() doesn't begin with the max(FK)+1 value.
>
For efficiency the only thing used to determine the next value of a
sequence is the stor
On Tue, Jun 11, 2024 at 2:36 AM Dominique Devienne wrote:
> Hi. I've noticed [that libpq API in v17 beta1][1], and wanted to use
> it to replace an existing Boost.ASIO-based async polling of the
> connection's socket, waiting for notifications. The use case being
> using PostgreSQL LISTEN/NOTIFY f
Over the years I've deleted rows from tables leaving gaps in the PK sequence
numbers. I've now learned that using nextval('sequencename') finds those
gaps and tells me that the value after the gap is already assigned.
I found a web page that explains how to find the gaps in a sequence, yet I
want
On Mon, 10 Jun 2024, David G. Johnston wrote:
Values introduces literal records/rows. Rows are written within
parentheses. Values (..., ...), (..., ...)
You seem to have the closing parenthesis but not the opening one.
David,
Duh! Of course. For whatever the reason I kept not seeing that.
M
On Mon, Jun 10, 2024 at 2:58 PM Rich Shepard
wrote:
>
> INSERT into companies (company_nbr,company_name,industry,status) VALUES
> nextval('companies_org_nbr_seq'),'Acme','Manufacturing','Opportunity'),
> nextval('companies_org_nbr_seq'),'Baker','Livestock','Opportunity');
>
> Running:
> $ psql -U
On Mon, 10 Jun 2024, Peter J. Holzer wrote:
My question is whether I can create new rows for all three tables in the
same sql source file.
Yes, of course.
Since the location and contact tables require sequence
numbers from the company and location tables is there a way to specify,
e.g., cur
On 2024-06-10 11:50:27 -0700, Rich Shepard wrote:
> My business tracking database has three main tables: company, location,
> contact. The company and contact primary keys are sequences.
>
> I've been adding new rows using INSERT INTO files separately for each table
> after manually finding the la
>
> On Mon, Jun 10, 2024 at 12:43 PM Ron Johnson
> wrote:
> Most useful to you will be some number of "ALTER TABLE DISABLE
> TRIGGER ALL;" statements near the beginning of the file, and their "ALTER
> TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file.
>
>
Have you just not he
On 6/10/24 10:51, Ron Johnson wrote:
On Mon, Jun 10, 2024 at 11:42 AM David G. Johnston
mailto:david.g.johns...@gmail.com>> wrote:
On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson mailto:ronljohnso...@gmail.com>> wrote:
"set standard_encoding_strings = on" is at the top, and there's
On Mon, Jun 10, 2024 at 4:06 PM Rich Shepard
wrote:
> On Mon, 10 Jun 2024, Ron Johnson wrote:
>
> > With enough clever scripting you can create a .sql file that does almost
> > anything.
>
> Ron,
>
> My projects don't all use SQL so I'm far from a clever scripter. :-)
>
No one is born a scripter
On 6/10/24 12:17, veem v wrote:
Hi, It's version 15.4 of postgres. We have a requirement to have the
audit enabled for the delete queries on the base table. And for that we
are planning to have one audit table created for each base table and
have triggers on each of the base tables to be fired
On 6/10/24 11:43, Dominique Devienne wrote:
Bummer… I didn’t presume to suggest an api before, but simply adding an
extra int with the milliseconds offset from the time_t is simple, and
trivial to plug into the implementation I saw. Callers who don’t care
can simply pass zero. while I could pas
On Mon, 10 Jun 2024, Torsten Förtsch wrote:
Something along these lines perhaps:
=# create table a( id bigserial primary key, x text );
CREATE TABLE
=# create table b( fk bigint references a(id), y text );
CREATE TABLE
=# with ins_a as (insert into a (x) values ('a row') returning *)
insert i
On Mon, 10 Jun 2024, Ron Johnson wrote:
With enough clever scripting you can create a .sql file that does almost
anything.
Ron,
My projects don't all use SQL so I'm far from a clever scripter. :-)
Most useful to you will be some number of "ALTER TABLE DISABLE
TRIGGER ALL;" statements near
On Mon, Jun 10, 2024 at 8:50 PM Rich Shepard
wrote:
> My question is whether I can create new rows for all three tables in the
> same sql source file. Since the location and contact tables require
> sequence
> numbers from the company and location tables is there a way to specify,
> e.g., current
On Mon, Jun 10, 2024 at 2:50 PM Rich Shepard
wrote:
> My business tracking database has three main tables: company, location,
> contact. The company and contact primary keys are sequences.
>
> I've been adding new rows using INSERT INTO files separately for each table
> after manually finding the
Hi, It's version 15.4 of postgres. We have a requirement to have the audit
enabled for the delete queries on the base table. And for that we are
planning to have one audit table created for each base table and have
triggers on each of the base tables to be fired on delete which will insert
records
My business tracking database has three main tables: company, location,
contact. The company and contact primary keys are sequences.
I've been adding new rows using INSERT INTO files separately for each table
after manually finding the last PK for the company and contact tables. The
location tabl
Bummer… I didn’t presume to suggest an api before, but simply adding an
extra int with the milliseconds offset from the time_t is simple, and
trivial to plug into the implementation I saw. Callers who don’t care can
simply pass zero. while I could pass a computed time_t and ms offset using
. No nee
On Mon, Jun 10, 2024 at 11:42 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson
> wrote:
>
>>
>> "set standard_encoding_strings = on" is at the top, and there's no other
>> reference to it.
>>
>>
> Well, if they are not using E-strings for esc
Dominique Devienne writes:
> PQsocketPoll() being based on time_t, it has only second resolution, AFAIK.
> Despite the [underlying implementation in fe-misc.c][2] supporting at
> least milliseconds.
> ...
> But I think it would a pity if that unreleased API couldn't be made to
> accomodate sub-sec
On Mon, 2024-06-10 at 14:06 +0200, Torsten Förtsch wrote:
> Now, my question is why does a vacuum backend have a backend_xmin? I am just
> curious.
I'd say because it sees a certain snapshot of the database, like all other
statements.
Yours,
Laurenz Albe
On Mon, Jun 10, 2024 at 8:19 AM Ron Johnson wrote:
>
> "set standard_encoding_strings = on" is at the top, and there's no other
> reference to it.
>
>
Well, if they are not using E-strings for escapes then you have the answer
why v14 is broken. Does it really matter why v9.6 apparently worked ev
On 6/10/24 07:52, Ron Johnson wrote:
On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston
mailto:david.g.johns...@gmail.com>> wrote:
This hasn't changed from 9.6, has it?
A Java app that uses backslash escapes broke this morning on fields with
single quotes, after the weekend migration from PG
On Mon, Jun 10, 2024 at 11:08 AM Tom Lane wrote:
> Ron Johnson writes:
> > On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
> > david.g.johns...@gmail.com> wrote:
> >> As the caution on that page says the default for standard conforming
> >> strings changed in 9.1. But maybe your 9.6 had the
Ron Johnson writes:
> On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>> As the caution on that page says the default for standard conforming
>> strings changed in 9.1. But maybe your 9.6 had the old value configured but
>> when you upgraded to 14 you deci
On Mon, Jun 10, 2024 at 10:56 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Monday, June 10, 2024, Ron Johnson wrote:
>
>> On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson
>>> wrote:
>>>
On Monday, June 10, 2024, Ron Johnson wrote:
> On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson
>> wrote:
>>
>>> PG 9.6 and PG 14
>>>
>>> https://www.postgresql.org/docs/14/sql-syntax-lexical.
>>> html#SQL-
On Mon, Jun 10, 2024 at 10:08 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson
> wrote:
>
>> PG 9.6 and PG 14
>>
>>
>> https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
>>
>> [quote]
>> Any other character follow
Hi. I've noticed [that libpq API in v17 beta1][1], and wanted to use
it to replace an existing Boost.ASIO-based async polling of the
connection's socket, waiting for notifications. The use case being
using PostgreSQL LISTEN/NOTIFY for a simple message queue. The code
needs to be cross-platform Wind
On Mon, Jun 10, 2024 at 7:07 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson
> wrote:
>
>> PG 9.6 and PG 14
>>
>>
>> https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
>>
>> [quote]
>> Any other character followi
On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson wrote:
> PG 9.6 and PG 14
>
>
> https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
>
> [quote]
> Any other character following a backslash is taken literally. Thus, to
> include a backslash character, write two backslashes (
PG 9.6 and PG 14
https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
[quote]
Any other character following a backslash is taken literally. Thus, to
include a backslash character, write two backslashes (\\). Also, a single
quote can be included in an escape string by wr
Hi,
This is a VACUUM FREEZE process.
-[ RECORD 1 ]--+--
pid| 129471
datid | 16401
datname| feed
relid | 1889166
phase | scanning heap
heap_blks_total| 1254901
heap_blks_scanned | 1017524
heap_blks_vacuumed | 0
Hi everyone
I run a PostgreSQL 14 environment with one primary and two standby instances
with streaming replication. I recently had a case where the streaming
replication on one of the standby instances paused (seemingly automatically):
2024-06-01 01:11:38.482 CEST [4575476] @ app=LOG: recover
superuser
Sent from Gmail Mobile
On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe
wrote:
> On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
> > how you can help me with lost super password?
>
> Are you talking about a PostgreSQL superuser password or
> about the "master password" that the pgAdm
On Fri, 2024-06-07 at 07:42 -0700, David G. Johnston wrote:
> On Friday, June 7, 2024, Laurenz Albe wrote:
> > On Fri, 2024-06-07 at 13:54 +, Zwettler Markus (OIZ) wrote:
> > > > Another point to keep in mind is that by default, execute privilege is
> > > > granted to
> > > > PUBLIC for newly
On Mon, 2024-06-10 at 05:06 -0400, Afa Jamal wrote:
> On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe wrote:
> > On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
> > > how you can help me with lost super password?
> >
> > Are you talking about a PostgreSQL superuser password or
> > about the "mas
Hi
Do following
Stop pg service
Open pg_hba.conf
Put trust for authentication e.g. "local all all
trust"
Start the service
Log in using psql e.g psql -U postgres
Change the password e.g ALTER USER postgres WITH PASSWORD 'new_password';
Regards
Kashif Zeesh
On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
> how you can help me with lost super password?
Are you talking about a PostgreSQL superuser password or
about the "master password" that the pgAdmin client uses
to encrypt connection information?
Yours,
Laurenz Albe
Sent from Gmail Mobile
-- Forwarded message -
From: Afa Jamal
Date: Fri, Jun 7, 2024 at 8:13 PM
Subject: lost master password
To:
Hello
how you can help me with lost super password?
thank you
--
*Afa J Zada*
51 matches
Mail list logo
