Re: pg_dump and not MVCC-safe commands

> On May 20, 2024, at 08:49, PetSerAl wrote: > Basically, you need application cooperation to make > consistent live database backup. If it is critical that you have a completely consistent backup as of a particular point in time, and you are not concerned about restoring to a different

Re: Updating 457 rows in a table

> On May 19, 2024, at 11:30, Rich Shepard wrote: > That's a good idea; I can use a predicate to identify the rows to update. > That would be shorter than a long, comma-separated list. Of course, you can probably also shorten the query to: UPDATE people SET active=true WHERE ...

Re: Updating 457 rows in a table

> On May 19, 2024, at 09:54, Rich Shepard wrote: > > Specifically, in the 'people' table I want to change the column 'active' > from false to true for 457 specific person_id row numbers. UPDATE people SET active=true WHERE id IN (...); The ... can either be an explicit list of the ids, or a

Re: UTC is not a time zone?

> On May 13, 2024, at 11:26, Adrian Klaver wrote: > May not induce the error unless there are parallel workers involved. Indeed. I'll see about pulling together a test case that forces that.

Re: UTC is not a time zone?

> On May 13, 2024, at 11:17, Tom Lane wrote: > What's causing that I can't say. It doesn't look like we log the > errno anywhere when failing to read a zone file :-( File descriptor exhaustion? (Of course, that would mean something somewhere is leaking them, which is another problem.)

Re: UTC is not a time zone?

> On May 13, 2024, at 11:07, Adrian Klaver wrote: > > > What does pg_config --configure show for '--with-system-tzdata' ? It's a local compile, and was built without that. As an experiment, I'm just pounding the server with a single connection doing nothing but SET TIMEZONEs repeatedly.

Re: UTC is not a time zone?

> On May 13, 2024, at 10:58, Adrian Klaver wrote: > > You sure the timezone file did not get changed under the connection? Yes (at least, nothing happened on the system that would indicate that). The system wasn't touched during the execution (and, as noted, it worked after as well as

Re: UTC is not a time zone?

> On May 13, 2024, at 10:53, Erik Wienhold wrote: > Can you trigger that error with: > > SET timezone = 'UTC'; No, that works correctly: psql (16.3) Type "help" for help. df=> SET timezone = 'UTC'; SET The error popped up during a long-running connection that had issued that SET many

Re: UTC is not a time zone?

> On May 13, 2024, at 10:48, Adrian Klaver wrote: > Yes: > > https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org > > Answer: > > https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us Thanks! Similar, but I don't think it's that.

UTC is not a time zone?

PostgreSQL 16.3 on MacOS Sonoma. A long-running process (which held a connection open the entire time) failed with: 2024-05-13 09:12:44.719 PDT,"cyan","cyan",35926,"[local]",664214f9.8c56,3,"SELECT",2024-05-13 06:26:17 PDT,3/60,0,ERROR,22023,"invalid value for parameter ""TimeZone"":

Re: Question regarding how databases support atomicity

> On May 3, 2024, at 20:02, Siddharth Jain wrote: > > > The way I understand this is that if there is a failure in-between, we start > undoing and reverting the previous operations one by one. But what if there > is a failure and we are not able to revert an operation. How is that >

Re: Performance degradation after upgrading from 9.5 to 14

> On Apr 17, 2024, at 10:13, Johnathan Tiamoh wrote: > I performed an upgrade from postgresql-9.5 to postgresql-14 and the > performance has degraded drastically. > > Please, is they any advice on getting performance back ? Run: VACUUM (ANALYZE, VERBOSE); More seriously (although

Re: What is referential_action?

> On Apr 8, 2024, at 06:37, Ron Johnson wrote: > > Four times, the word "referential_action" is used on this page, but it's > never mentioned what the possible referential actions are. They're defined in CREATE TABLE: https://www.postgresql.org/docs/14/sql-createtable.html

Re: how to check if the license is expired.

> On Mar 31, 2024, at 09:59, Peter J. Holzer wrote: > Is this an acceptable performance penalty per API call? If not, is it > really necessary to check this on every call? Maybe it can be done just > once per session or once per hour. It's probably not required to check it every API call.

Re: How to reference a DB with a period in its name ?

> On Mar 29, 2024, at 14:16, David Gauthier wrote > I tried encapsulating the DB name in double quotes (no good), single quotes > (still no good) escaping with '\' (no good), escaping with ".." (no good). This is probably more about the string handling in the API you are using than

Re: User roles for gathering performance metrics data

> On Mar 26, 2024, at 22:30, Siraj G wrote: > I am from Oracle background. In Oracle, we grant select_catalog_role or > select any dictionary role to users who want to study performance data. I am > trying to get similar information on the roles or privileges in PgSQL that we > might want

Re: No warning for a no-op REVOKE

> On Mar 25, 2024, at 07:20, Daniel Gustafsson wrote: > >> On 25 Mar 2024, at 15:09, Tom Lane wrote: > >> My initial reaction is that we should warn only when the command >> is a complete no-op, that is none of the mentioned privileges >> matched. > > That's my gut reaction too, I think

No warning for a no-op REVOKE

Right now, if you do a REVOKE that doesn't actually revoke anything, it works silently. This can be a bit of a foot-gun. For example: CREATE FUNCTION f() RETURNS int as $$ SELECT 1; $$ LANGUAGE sql; REVOKE EXECUTE ON FUNCTION f() FROM lowpriv; Naively, it might be expected

Re: Is this a buggy behavior?

> On Mar 25, 2024, at 02:50, Thiemo Kellner wrote: > My bad. I was under the impression that the create table statement was an > atomic process/transaction with all its bells and whistles for constraints > and keys, instead of a succession of alter statements. That may be a bit judgmental.

Re: Is this a buggy behavior?

> On Mar 24, 2024, at 09:32, Thiemo Kellner wrote: > Am 24.03.2024 um 17:15 schrieb Christophe Pettus: >> I think the point is that it's not really doing anything "silently." You >> are asking for a PRIMARY KEY constraint on a column, and it's giving it to

Re: Is this a buggy behavior?

On 3/24/24 08:28, Thiemo Kellner wrote: > Sure, my example has lots more side effect than silently do the right thing. I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on a column, and it's giving it to you. One of the effects

Re: Seq scan vs index scan

> On Mar 22, 2024, at 20:55, arun chirappurath wrote: > I am trying to force query to use indexes using query hints. PostgreSQL does not have query hints. Enabling index scans using parameters doesn't *disable* other types of query nodes. You can disable sequential scans using:

Re: Timing out A Blocker Based on Time or Count of Waiters

> On Mar 22, 2024, at 09:25, Fred Habash wrote: > > Facing an issue where sometimes humans login to a database and run DDL > statements causing a long locking tree of over 1000 waiters. As a workaround, > we asked developers to always start their DDL sessions with 'SET lock_timeout > =

Re: Dropping a temporary view?

> On Mar 20, 2024, at 09:51, Celia McInnis wrote: > > The view is being used in some web query software that multiple people will > be accessing and the contents of the view depend on what the person is > querying, so I think that temporary views or tables are a good idea. There's nothing

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

> On Mar 19, 2024, at 19:56, Celia McInnis wrote: > > Thanks for the suggestion, Steve, but No - when I insert 25:17:07::interval > into my table I get 01:17:07 into the table - i.e., it replaces 25 hours by > (25 mod 24) hours or 1 hour, and this is not what I want. I really need the >

Re: select results on pg_class incomplete

> On Mar 15, 2024, at 03:30, Thiemo Kellner wrote: > Thanks for the ideas. As I would want to keep it in the database, dblink > would be the way to go. Maybe, I will create a prodedure that creates a view > in the monitor schema accessing the respective databases with union all to >

Re: Receipt for PostgreSQL US Invoice #1840

> On Mar 12, 2024, at 19:27, Adrian Klaver wrote: > > Oops? Oops. Apologies for the mis-forward.

Fwd: Receipt for PostgreSQL US Invoice #1840

Begin forwarded message:From: Sadie Bella Subject: Fwd: Receipt for PostgreSQL US Invoice #1840Date: March 12, 2024 at 19:13:40 PDTTo: Christophe -- Forwarded message -From: Date: Tue, Mar 12, 2024, 7:07 PMSubject:

Re: Is it possible to keep track of SELECTs?

> On Mar 12, 2024, at 07:15, Dominique Devienne wrote: > So is it possible to track the last time a SELECT was performed on some TABLE? Directly, no. You could periodically sample the various table-level statistics, and conclude that tables that have had some type of scan since the last

Re: update to 16.2

> On Mar 8, 2024, at 00:53, Matthias Apitz wrote: > It does not say definitely that for all other versions a dump/restore is > required. You cannot just replace the binaries to upgrade from an earlier major version to 16.X. The release notes use "a dump/restore (is/is not) required" to

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

> On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud > wrote: > So, I ask, have there been any efforts to bring PL/PGSQL to the terminal? Strictly speaking, of course, you can use PL/pgSQL from the terminal already: just use psql, connect to the database, and create and run functions and

Re: extract ddl to devops pipeline

> On Mar 6, 2024, at 13:18, Lorusso Domenico wrote: > So there is a way to automatically generate DDL in the right order? Standard pg_dump creates files that are in the proper order, although if you exclusive some tables or schemas from the backup, those might cause errors if references

Re: How to add columns faster

> On Mar 3, 2024, at 12:00, Christophe Pettus wrote: > Remember that dropping the NULL constraint afterwards will require a full > table read (although not a rewrite). Sorry, badly put: Adding a NOT NULl constraint afterwards will require a full table read (although not a rewrite).

Re: How to add columns faster

> On Mar 3, 2024, at 11:40, yudhi s wrote: > Thanks for the clarification. In case of adding the column as volatile > default (like current_timestamp function as default) or say adding NOT NULL > column with some conditional population of existing values will be a full > table rewrite. In

Re: How to add columns faster

> On Mar 3, 2024, at 11:06, yudhi s wrote: > as the column addition using the traditional "Alter table" command in > postgres looks to be a full table rewrite That's not always (or, really, usually) true. Adding a new column in any recent version of PostgreSQL just alters the system

Re: Exclude certain application pgaudit logging?

> On Feb 6, 2024, at 10:11, Ron Johnson wrote: > Thus, I'd like to exclude reads from "Postgresql JDBC Driver". (Currently, I > filter that out using "grep -v" in a shell script that runs hourly from cron, > but I find that unsatisfactory.) pgAudit doesn't currently include filters by

Re: Best practices for data buffer cache setting/tuning (v15)

> On Jan 29, 2024, at 11:39, David Gauthier wrote: > > Is there a document which makes recommendations on sizing data buffer cache, > tuning options which evict old/unused data in mem, and cache fragmentation > avoidance for a v15.3 DB ? On any modern system, set shared_buffers to 25% of

Re: Seeking help extricating data from Amazon RDS Aurora/Postgres

> On Jan 29, 2024, at 11:22, Bill Mitchell wrote: > > Wondering if any of the other members of this LISTSERV have tried migrating > their data off of Amazon RDS Aurora Postgres with success. Any logical-replication based solution (DMS, fivetran, in-core logical replication) will handle the

Re: Clarification regarding managing advisory locks in postgresql

> On Jan 24, 2024, at 19:17, Sasmit Utkarsh wrote: > > Need your support on understanding advisory locks in Postgresql and what is > the best practice to have advisory locks and unlocks to work properly when we > have multiple process forked from single process? Advisory locks are a shared

Re: Changing a schema's name with function1 calling function2

> On Dec 25, 2023, at 10:44, Adrian Klaver wrote: > Functions with same name in different schemas would need to be dealt with. I think that's the primary use-case (at least, it would be for me), and I don't see a convenient way of doing that. Even a "get OID of current function" function

Re: Help understand why DELETE is so slow

> On Dec 1, 2023, at 09:47, Ping Yao wrote: > Can someone help me understand why my simple DELETE query is so slow to run? Based on the plan, you're running PostgreSQL with the Citus extension, and the delay is in Citus-related code. This is probably a question best directed to either the

Re: Ways to aggregate table info from variable number of schemas

> On Nov 27, 2023, at 10:16, Dominique Devienne wrote: > Which means you can't do a declarative SQL query for those > metadata across projects, since you can't do static / non-dynamic SQL across > schemas. I'm not sure I understand this. Schemas are just namespaces, and all queries have

Re: Can user specification of a column value be required when querying a view ?

> On Nov 20, 2023, at 13:41, David Gauthier wrote: > I want the users to be required to provide a value for ssn in the following > query... > "select * from huge_view where ssn = '106-91-9930' " > I never want them to query the view without specifying ssn. > It has to do with resources and

Re: Aren't regex_*() functions built-in?

> On Nov 11, 2023, at 17:20, p...@pfortin.com wrote: > Actually, it's more eusbtle... I can make it work as "postgres"; but not > as a RO user (SELECT only): > An error occurred when executing the SQL command: > select * from a,b where regexp_replace(a.address,' ','','g') = >

Re: Postgres limitation in user management

> On Nov 2, 2023, at 23:26, Kar, Swapnil (TR Technology) > wrote: > We want Support users to have no SELECT or DML privilege but only ALTER TABLE > to perform any troubleshooting in the database. If a user has no ability to do SELECT or DML, they won't be able to "troubleshoot" the

Re: purpose of an entry in pg_hba.conf file

> On Oct 26, 2023, at 11:53, Atul Kumar wrote: > > Please share the required link having such information in detail, It would be > more helpful to me. https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Re: purpose of an entry in pg_hba.conf file

> On Oct 26, 2023, at 11:44, Atul Kumar wrote: > There is already one line to serve your stated purpose > local all alltrust > > > That's why I specifically raised this question for below from postgresql > experts > hostall all

Re: Problem with CAST-ing - am I missing something?

> On Oct 25, 2023, at 17:21, Pól Ua Laoínecháin wrote: > > SELECT (ts, te)::TSTZRANGE FROM test; That syntax doesn't mean what you probably think it does. (ts, te) defines a record type with two fields. PostgreSQL constructs that, and then attempts to apply the cast. There's no

Re: setting up streaming replication

> On Oct 24, 2023, at 11:31, Brad White wrote: > Are you saying that once I get streaming replication set up, it quits working > when I reboot the servers once a week? Not unless the downtime is sufficiently long that the replica can't find the WAL information it needs. You can avoid this

Re: PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This

> On Oct 23, 2023, at 04:45, Achilleas Mantzios - cloud > wrote: > I believe this text is false on too many accounts. So, what's the consensus > about Inheritance in PostgreSQL, I am going to give a talk on it in November > and I wouldn't like to advertise/promote/teach something that the

Re: Purely declarative FKs

> On Oct 16, 2023, at 00:51, Thiemo Kellner wrote: > Question: Are there plans to provide a feature in PostgreSQL that one can > have foreign keys for purely documentation purpose - I know, one could use a > modelling tool and just not implement the FKs, but my reality is, there is > hardly

Re: Database selection

> On Sep 20, 2023, at 14:11, veem v wrote: > > Does AWS aurora postgres depend on the same vacuuming technology for > maintaining the transactions? Yes. Aurora has replaced the PostgreSQL storage engine, but the MVCC part is largely the same. The issues with vacuuming are largely

Re: [EXTERNAL] Oracle FDW version

> On Aug 22, 2023, at 19:57, Jethro Elmer Sanidad > wrote: > > Hello, > > I tried both the 1.5.0 and 2.0.0. Both returned error during 'make' command. > Please see below: The API between PostgreSQL and foreign data wrappers has changed significantly since 9.4. As Tom mentioned, you need

Re: Dropping all tables in a database

> On Aug 6, 2023, at 18:17, H wrote: > > Is there some setting I have to change in the database to have the first SQL > statement to work or have I run into a possible bug? The first statement just generates a line of text output that contains the statement. There's nothing in it that

Re: Sample pg_hba.conf allows local users to access all databases

> On Aug 1, 2023, at 10:13, William Edwards wrote: > This allows all local users connecting over TCP to access all databases, not > only the databases that the user is a member of as one might expect. There's really no notion of a user being "a member of" a database in PostgreSQL. Users

Re: Using "exit" to bring "goto" functionality.

> On Jul 10, 2023, at 11:54, Bryn Llewellyn wrote: > > What is the rationale for supporting what seems to be on its face this > strange functionality? It allows you to EXIT or CONTINUE a loop that is not the innermost one, by naming the label of an outer loop. One can debate endlessly

Re: Trigger Function question

> On Jul 10, 2023, at 11:46, DAVID ROTH wrote: > > Is there a way to get new.* into a jsonb column? The to_jsonb() function accepts a row type like NEW.*, and returns a JSONB object with the keys as column names.

Re: Trigger Function question

> On Jul 10, 2023, at 11:37, DAVID ROTH wrote: > > Thanks for the example. I have a test trigger now that does that but my > application needs all of the columns. I'm not quite sure I understanding. Logging NEW.* and OLD.* *does* get all the columns, without having to specific query to

Re: Trigger Function question

> On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > I want to use a single trigger function to log multiple tables and the tables > have different columns. I can get the names of the columns from the catalog. > But I have not been able to figure out how to get NEW.x when x is not known >

Re: Trigger Function question

> On Jul 10, 2023, at 11:20, DAVID ROTH wrote: > > In a trigger function, is there a way to get a list of all of the columns in > the triggering table? You can get the table that the trigger fired on with TG_TABLE_SCHEMA and TG_TABLE_NAME, and then query the system catalogs to get a list

Re: INSERT UNIQUE row?

A UNIQUE index can have any number of columns, so you can create an index with all of the appropriate columns listed. This is different from having a UNIQUE index on each individual column. In the former case, all of the columns together must be unique; in the latter case, as you mention,

Re: SOC II Type 2 report.

Hi, > On Jun 12, 2023, at 11:57, Raj Kiran wrote: > Prokopto is completing our annual vendor review process. Please share your > most recent SOC II Type 2 report. The PostgreSQL project isn't SOC2 certified, and will almost certainly never be. If you require SOC2 compliance, you'll need to

Re: pg_upgrade and schema complexity...

> On Jun 2, 2023, at 17:44, Ron wrote: > Is this to be expected of such a huge schema? pg_upgrade time is pretty much proportional to the number of database objects in the schema, so a much larger schema taking much longer is to be expected.

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

> On May 22, 2023, at 13:06, Adrian Klaver wrote: > As I understand TDE whether you can get to the files is not really the point. > It is that someone/thing can and if they do the files are encrypted. Pretty > sure RDS is not magical enough to have no access from any source to the file >

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

> On May 22, 2023, at 11:02, Tony Xu wrote: > there are still some shared area between clusters. That's not quite right. A PostgreSQL cluster (in the traditional sense, which means one PostgreSQL server handling a particular endpoint) is isolated from any other clusters on the same

xid type? (was :)

> On May 15, 2023, at 08:41, Fabrice Chapuis wrote: > What is the xid type and how can I cast integer value to make > pg_xact_commit_timestamp to work? The xid type is... xid. You'll need to cast as a string instead of an integer: xof=# select pg_xact_commit_timestamp('53013547'::xid);

Re: What type of Compiler to SQL? Memory-Image (Load-and-Go) Format?

> On May 4, 2023, at 18:00, Wen Yi wrote: > > Hi team, > I am a newbie to the postgres. > When I am studying the compiler,the text book tell me there is there type of > compiler. > • Assembly Language Format > • Relocatable Binary Format > • Memory-Image (Load-and-Go)

Re: Regarding SSL Enablement in PostgreSQL Database on different port

> On May 2, 2023, at 12:15, Tomas Pospisek wrote: > > Oh, I think your idea to use pgbouncer to take care of the SSL termination is > elegant. I don't think me I'd characterize it as a hack if properly set up. > Why do you consider it a hack? It's really only a hack in the sense that

Re: Postgres query doesn't accept double quote around schema name in query statement

> On Apr 27, 2023, at 12:40, Michael Xu wrote: > In our env, it throws 42P01:relation "ads.MyTableName" does not exist. The function of double quotes in SQL is to allow you do include characters that would otherwise not be legal in an identifier (as well as making the identifier

Re: VACUUM (INDEX_CLEANUP OFF) and GIN indexes

> On Apr 25, 2023, at 09:35, Peter Geoghegan wrote: > > It's skipped by VACUUM, but not by ANALYZE. So if you're using the > reloption version of index_cleanup=off, it isn't necessarily going to > stop autovacuum/autoanalyze from doing pending list cleanup. Ugh, thanks. I wasn't aware that

VACUUM (INDEX_CLEANUP OFF) and GIN indexes

Does VACUUM (INDEX_CLEANUP OFF) flush the pending list for GIN indexes, or is that skipped as well?

Re: Logical Replication Sync Validation

> On Apr 18, 2023, at 03:45, Robert Sjöblom wrote: > I'm aware of that. But you can, however, do something like: > > SELECT * FROM FOO WHERE CTID = (SELECT MAX(CTID) FROM FOO); > > on both sides. The idea being that if I change FOO, the CTID of the changed > row will not be the same on both

Re: Logical Replication Sync Validation

> On Apr 18, 2023, at 01:20, Robert Sjöblom wrote: > Another idea we've had would be to use CTID to fetch the last row > (update/insert) in each table on both sides and compare row content, is this > feasible? Is it safe to rely on CTIDs across logical replication? No. CTIDs aren't sent

Re: Using CTID system column as a "temporary" primary key

> On Mar 29, 2023, at 12:11, Sebastien Flaesch > wrote: > But to make PostgreSQL more Informix-compatible, zero should have been > considered as well. There is an infinite family of strange features that various databases have (DUAL from Oracle, anyone?); PostgreSQL will rapidly become

Re: Using CTID system column as a "temporary" primary key

> On Mar 28, 2023, at 03:39, Sebastien Flaesch > wrote: > Do I have to cast() ? Yes: select * from t where ctid='(0,1)'::tid; The string representation can be up to 17 characters: 10 for the page number, 4 for the tuple number, and three for the delimiters. Remember that updating

Re: Oracle to PostgreSQL Migration

> We have an Oracle DB which is around 1TB and we want to migrate to > PostgreSQL that have a new table structure, so we want to perform > data transformation and real time CDC from Oracle to PostgreSQL. Do > we have any good open source tool to achieve this with No Coding > involved.?? To meet

Re: How does Postgres store a B-Tree on disk while using the OS file system?

> On Mar 6, 2023, at 16:24, Siddharth Jain wrote: > My question: How can it then store a B Tree on disk? I would think storing a > B Tree requires storing disk offset addresses and so on (for a node to > navigate to another etc.). For this, one would need to write directly to the > disk

Re: Is Autovacuum running?

> On Feb 21, 2023, at 10:48, Brad White wrote: > > Running the table_bloat_check query from here > https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql > > shows some tables with over 20MB and over 20% bloat while my threshold is set > to 0.1.

Re: Is Autovacuum running?

> On Feb 21, 2023, at 09:54, Brad White wrote: > Any suggestions on how to proceed? First, look at pg_stat_user_tables to see how many inserts etc. have occurred on the tables that are not showing an autovacuum; they may have simply not reached the threshold yet. If they have, do a VACUUM

Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

> On Feb 20, 2023, at 17:54, Bryn Llewellyn wrote: > > > I’ve no idea how I might have found this without human help. That sounds like an excellent documentation patch!

Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

> On Feb 20, 2023, at 11:57, Bryn Llewellyn wrote: > 2. If I send over "begin" and then "insert into s.t(v) values(42)", then (so > far) a second session will not see the effect of my SQL's. It sees this only > when I send over "commit". (If I send over "rollback" instead of "commit", >

Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

> On Feb 18, 2023, at 18:52, Ian Lawrence Barwick wrote: > > Historical trivia: PostgreSQL had a (backend) "autocommit" GUC in 7.3 > only, which remained as > a dummy GUC until 9.5 (see: https://pgpedia.info/a/autocommit.html ). Well, that was a pretty whacky idea. :-)

Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

> On Feb 18, 2023, at 15:49, Bryn Llewellyn wrote: > > I’ve searched in vain for an account of how "autocommit" mode actually works. I realize now I may have misinterpreted your question... apologies if so! If you mean the BEGIN and COMMIT statement that some client libraries insert into

Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

> On Feb 18, 2023, at 15:49, Bryn Llewellyn wrote: > > Or is it done server-side? It's done server-side. Note that what really happens is that, when a statement begins execution and there is no open transaction, a snapshot is taken and then released when the statement finishes (just as

Re: Does Postgres 14 have a query cache?

> On Feb 18, 2023, at 06:59, Peter J. Holzer wrote: > I think the reason > is that the use case (the exact same query is submitted repeatedly) is > sufficiently rare that it isn't all that effective in practice. And, in this use case, a prepared statement is in effect a cache of the parsing

Re: How to use the BRIN index properly?

> On Feb 8, 2023, at 14:14, Siddharth Jain wrote: > > If the B-Tree index will be so large that it cannot fit in memory, then is it > worth creating it at all? Yes. Of course, more memory is better, and more recently versions of PostgreSQL have optimizations that are valuable for large

Re: How to use the BRIN index properly?

> On Feb 8, 2023, at 13:17, Siddharth Jain wrote: > > As I explained in my question that is indeed our dilemma. Our insertion order > will not be equal to index order. i.e., referring to your response: > > > who's data is added in the same order as the key in the BRIN index > > does NOT

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

> On Feb 1, 2023, at 10:45, David G. Johnston > wrote: > The system just isn't that intelligent for "sequential scan", instead it does > literally what the label says, goes through the table one page at a time and > returns any live rows it finds. Although this does raise a question:

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

> On Jan 31, 2023, at 07:40, Dimitrios Apostolou wrote: > Is this bloat even affecting queries that do not use the index? No, but a bloated index often (although not always) goes along with a bloated table. > It seems I have to add VACUUM FULL to nightly maintainance. I wouldn't go that

Re: Why is this SELECT evaluated?

> On Jan 28, 2023, at 11:29, Miles Elam wrote: > Why does this error result? While the standard order of operations is to evaluate the WHERE before the SELECT list, it's not guaranteed to result in short-cut execution. In particular, constant folding happens very early in the processing of

Re: Indexes mysteriously change to ON ONLY

> On Jan 27, 2023, at 13:01, Rumpi Gravenstein wrote: > > We are using the pg_indexes view (indexdef) to retrieve the index definition. This is as expected. Once the index is created on the partitioned set of tables, the index on the *root* table will be ON ONLY that table; the child

Re: Sequence vs UUID

> On Jan 26, 2023, at 11:17, veem v wrote: > So we want to understand from experts here, if there are any clear rules > available or if we have any pros vs cons list available for each of those to > understand the exact scenario in which we should go for one over other? Clear rules are a

Re: [EXT] DBeaver session populating pg_stat_activity.backend_xmin

> On Jan 25, 2023, at 14:21, Dirschel, Steve > wrote: > From my perspective "idle in transaction" isn't necessarily a problem > (although I don't like seeing sessions sitting like that for a long time). > The problem is when pg_stat_activity.backend_xmin is populated- that can > prevent

Re: Is a logical replication crash recovery safe?

> On Jan 24, 2023, at 07:37, Zwettler Markus (OIZ) > wrote: > > Is a logical replication crash recovery safe? You might want to give more detail about the scenario you are concerned about. Changes that are received by a subscriber as part of logical replication are WAL-logged, observe

Re: No function matches the given name and argument types.

> On Jan 16, 2023, at 09:53, David G. Johnston > wrote: > > I don't see any good way to say: "given this function signature, and the fact > it cannot be found, what are the next closest function signatures that are > present". I can see a use-case for such functionality, though: A "did

Re: EXPLAIN and FK references?

> On Jan 11, 2023, at 22:07, Tom Lane wrote: > No, not directly, but you could look at EXPLAIN ANALYZE to see which > of the RI triggers is eating the time. It's not going to be hard to > figure out which one(s) are using indexed plans and which are not. Also, IIRC, the SELECTs generated to

Re: postgres restore & needed history files

> On Jan 5, 2023, at 21:28, Laurenz Albe wrote: > > On Tue, 2023-01-03 at 16:03 +, Zwettler Markus (OIZ) wrote: >> We are using a DIY Postgres backup: >> --- >> psql -c "select pg_start_backup ('Full');" >> save -s "${NSR_SERVER}" -g "${NSR_POOL}" "${PGDATA}" >> psql -c "select

Re: PG replicas and transactions atomicity

> On Jan 5, 2023, at 19:38, Bhautik Chudasama > wrote: > > Does it mean when we commit transaction, client will wait until all replicas > successfully committed the transaction. It depends. If all the settings are the defaults, no, the client won't wait for the replicas to acknowledge

Re: PG replicas and transactions atomicity

> On Jan 5, 2023, at 16:03, Ron wrote: > > Even in asynchronous replication? Yes. Asynchronous replication controls when the client doing the transaction is told that the transaction is complete; it doesn't allow for dirty reads on either the primary or secondary.

Re: PG replicas and transactions atomicity

> On Jan 5, 2023, at 12:07, Louis Laborde wrote: > > Are PG replicas updated atomically following the same transactions boundaries > as the source DB ? Yes. The same transactional guarantees apply to the replica as do to the original transactions on the primary.

  1   2   3   >