Resetting the lock_timeout value for a transaction

'; SHOW lock_timeout; -- Reset BOTH the SESSION and Transaction lock_timeout (both go back to 0, the -- default). RESET lock_timeout; SHOW lock_timeout; COMMIT; -- Should now be 0s because it was reset inside the transaction. SHOW lock_timeout; Thanks, Marcelo

Changing default fillfactor for the whole database

ssed something about being able to change this on a database level? Thanks, Marcelo.

pg_get_serial_sequence not working for manually set seq

Hi folks, I've been testing the pg_get_serial_sequence function and noticed that I can only get reliable results when using a SERIAL or IDENTITY column. However, shouldn't it work for manually set sequences too? In the docs[0] we have that this function: > Returns the name of the sequence assoc

Best way to check if a table is empty

. Uses a widespread and intuitive operation (count) Cons: 1. Very slow on large tables as it performs a Sequential Scan. How does all of that sound? Are there further strategies I should consider? Anything I have missed in the Strategies above? Regards, Marcelo.

Default Value Retention After Dropping Default

Hi folks, I am experiencing an interesting behavior in PostgreSQL and would like to seek some clarification. In the following snippet, I first add a column with a default value, then drop that default. However, when I query the table, the column still retains the dropped default for existing rows

Re: Best Approach for Swapping a Table with its Copy

On Sat, Feb 15, 2025 at 4:12 AM Greg Sabino Mullane wrote: > The pg_repack link posted earlier has the details on how it is done. But > messing with system catalogs like this is highly discouraged, for good > reasons. Still, if you need to go that route, test heavily and post the > solutions he

Re: Best Approach for Swapping a Table with its Copy

NOT VALID? That's an interesting compromise I haven't thought of. Thanks. However, ideally I'd like to swap the catalogue entries instead - as that would be a cleaner approach since it wouldn't require dropping old constraints, creating NOT VALID ones, and then optionally validating them later. Regards, - Marcelo

Re: Best Approach for Swapping a Table with its Copy

what I am trying to solve. If you have pointers, thoughts, or resources where I can better understand what's involved, that would be much appreciated. In terms of where I am at currently, I summarised in my previous reply: On Fri, Feb 14, 2025 at 11:59 AM Marcelo Fernandes wrote: > The p

Re: Best Approach for Swapping a Table with its Copy

t decide not to do pursue it in the end, but first I must understand (-: Regards, - Marcelo

Re: Best Approach for Swapping a Table with its Copy

a test > sample of data? Do you mean alternatives to table-cloning-and-swapping? Regards, - Marcelo

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 10:40 AM Adrian Klaver wrote: > Do you know this will not fail on the existing data? Yes, all the current data in the original table respects the constraint. > Do you have room for a complete copy of the table? Yes, in this scenario the copy is already created, and trigg

Re: Best Approach for Swapping a Table with its Copy

This is done to avoid having to hold an exclusive lock for a long amount of time, thus creating application outages. Hope that clarifies the situation a bit better - Marcelo

Best Approach for Swapping a Table with its Copy

doing this swap? Specifically related to foreign key integrity and TOAST data? Thank you! - Marcelo

Re: Logging queries executed by SPI_execute

nctions, I can't really see those functions being propagated to the logs. I have a workaround in place, which is to add elog calls such as elog(LOG, "...") to capture the queries. But of course, ideally a postgres.conf setting would be more handy. Best, Marcelo

Re: What is the story behind _SPI_PLAN_MAGIC?

On Mon, Feb 3, 2025 at 3:17 PM Tom Lane wrote: > Just to catch programming errors, ie passing the wrong pointer > value to some SPI function. See the checks for it in spi.c. Aha! Perfect, I thought it was something like that. Thank you! Marcelo.

What is the story behind _SPI_PLAN_MAGIC?

Hi there, Reading through the SPI code I see this definition: #define _SPI_PLAN_MAGIC 569278163 Which is used in he _SPI_plan struct in src/include/executor/spi_priv.h: typedef struct _SPI_plan { int magic; ... } What is its purpose? Thank you. Marcelo

Logging queries executed by SPI_execute

ements too. Is there anything I am missing? Some configuration or perhaps another way to find out about these statements? It may not be relevant, but the extension I am having a look at is pg_repack if anyone is interested. I am trying to get a deeper understanding of what the function repack_apply does. Thanks, Marcelo.

Re: RESET, NULL and empty-string valued settings and transaction isolation

Thank you both for the explanations and the link to the discussion of Pavel's patches.

RESET, NULL and empty-string valued settings and transaction isolation

d the docs, the effect isn't very nice because SQL like current_setting('my.some_boolean_setting')::boolean will fail after a transaction with SET LOCAL sets it, a side-effect that can be particularly confusing and basically requires usage of nullif(.., '') or other explicit checks around every current_setting call-site in practice. Thanks in advance, Marcelo.

Re: ERROR: could not open relation with OID XXXX

problem, from retrying like Tomas suggested to materialized CTEs that filter out temporary relations before functions like pg_get_indexdef are called. I will give these things a shot. Regards. On Sun, Aug 25, 2024 at 1:13 PM Adrian Klaver wrote: > On 8/25/24 08:36, Marcelo Zabani wrote: &

Re: ERROR: could not open relation with OID XXXX

Regards. On Sun, Aug 25, 2024 at 12:06 PM Tomas Vondra wrote: > On 8/25/24 15:42, Marcelo Zabani wrote: > > Hi all, > > > > I can reproduce the error in the subject from time to time when querying > > catalog tables while DDL is happening concurrently. Here's a

Re: ERROR: could not open relation with OID XXXX

t be possible depending on query plan, I believe. But again, an untested hypothesis. On Sun, Aug 25, 2024 at 11:31 AM Ron Johnson wrote: > On Sun, Aug 25, 2024 at 9:42 AM Marcelo Zabani wrote: > >> Hi all, >> >> I can reproduce the error in the subject from time to time w

ERROR: could not open relation with OID XXXX

roduce. Is there something I can do to avoid this? Is my understanding of how the catalog tables work wrong? Thanks, Marcelo.

Can't Remote connection by IpV6

Hi everyone, is it possible to remote connect through IpV6? IpV4 works fine but I cant connect through V6 postgresql.conf is to listen all address and pg_hba.conf is set with host all all :: md5 i've tried ::/0 and ::0/0 but had no success my provider is out of ipv4 and they're sending ips by cgn

Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

gdal-libs package belongs to PostGIS PostGIS Support Ticket #5664 - https://trac.osgeo.org/postgis/ticket/5664#ticket On Tue, Feb 6, 2024 at 10:12 AM Marcelo Marques wrote: > I appreciate the reply. > We have an internal Satellite that we pull from the EPEL repo. > The Satellite

Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

:18 AM Devrim Gündüz wrote: > Hi, > > On Mon, 2024-02-05 at 08:16 -0800, Marcelo Marques wrote: > > Yes, the EPEL repo is enabled. > > (Please keep the list CC'ed) > > Armadillo 12 packages *are* in the EPEL repo: > > https://dl.fedoraproject.org/pub/epel/9/Ev

Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

BUG #18328: yum update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86 <https://www.postgresql.org/message-id/18328-1e16fac373918f71%40postgresql.org> On Fri, Feb 2, 2024 at 11:05 AM Marcelo Marques wrote: > *PROBLEM* > > *yum update n

Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

1 root root 14013 Oct 17 04:10 pgdg-redhat-all.repo.old -rw-r--r--. 1 root root 4657 Jan 31 16:09 redhat.repo [root@rhel9-pg14 ~]# Thanks, Marcelo Marques Principal Product Engineer, Esri, www.esri.com

Re: Re: Problem enabling LDAP login

Thank you joão, that solved the problem!

Problem enabling LDAP login

I'm having some trouble configuring ldap login to postgres. I have configured LDAP on pg_hba.conf and postgres picks up the correct configuration during login but I get an error message whenever I attempt to login with psql to a database named teste. psql: error: could not connect to server: FATAL

Clarification on the release notes of postgresql 12 regarding pg_upgrade

There are a few instances where the release notes seem to indicate that the administrator should use pg_dump to upgrade a database so that improvements on btree can be available. Here are they: 1. >In new btree indexes, the maximum index entry length is reduced by eight bytes, to improve handlin

In-depth commercial postgresql training

Our company is looking for commercial training in postgresql. We want a training option that's as in-depth as possible (going as far as being able to read and patch postgresql source code). Is there any company that offers something like that? Another important thing to mention is that due to lega

Re: ORM

On 29/09/2018 16:09 , Edson Carlos Ericksson Richter wrote: Em 28/09/2018 19:49, marcelo escreveu: For a new big and convoluted project I (am/was) using Devart´s LinqConnect as ORM. But today I experienced some inexplicable "object reference not set to an instance of an object"

Re: ORM

On 28/09/2018 21:39 , Adrian Klaver wrote: On 9/28/18 3:49 PM, marcelo wrote: For a new big and convoluted project I (am/was) using Devart´s LinqConnect as ORM. But today I experienced some inexplicable "object reference not set to an instance of an object" exceptions or other mor

ORM

For a new big and convoluted project I (am/was) using Devart´s LinqConnect as ORM. But today I experienced some inexplicable "object reference not set to an instance of an object" exceptions or other more specific to this libraries. I would wish to change the ORM. Some experiences would be appr

Re: Select into table%ROWTYPE failed

Ohh, you are right! Thank you! On 18/09/2018 14:10 , Tom Lane wrote: marcelo writes: What was wrong in the first approach? plpgsql's "SELECT INTO" expects a one-for-one match between the output columns of the SELECT and the columns of the INTO destination. So I'd expect

Select into table%ROWTYPE failed

I´m testing a trigger function in a 9.4 installation. It´s for bill number assignation, but with a twist: there are various numbering ranges. This ranges are defined by a text code, a minimum and maximum. Every bill have some code taken from the set defined in a specific table (billnumberrange)

Re: Logical locking beyond pg_advisory

On 17/09/2018 14:27 , Chris Travers wrote: On Mon, Sep 17, 2018 at 6:04 PM marcelo <mailto:marcelo.nico...@gmail.com>> wrote: I´m using an ORM (Devart´s) to access the database, so, I cannot "select ... FOR UPDATE". The application paradigm is that a user

Re: Logical locking beyond pg_advisory

On 17/09/2018 12:21 , Chris Travers wrote: On Mon, Sep 17, 2018 at 5:09 PM Merlin Moncure <mailto:mmonc...@gmail.com>> wrote: On Sun, Sep 16, 2018 at 3:53 PM marcelo mailto:marcelo.nico...@gmail.com>> wrote: > > I need a mechanism of "logical lock

Re: Logical locking beyond pg_advisory

e the id was xored. I tested five or six table names, along 2 ids every one, without collision. But... Of course, I need the "full table lock" for inserts. So, it´s a very separated concern with updates and deletions. But... TIA On 17/09/2018 03:19 , Fabrízio de Royes Mello wrote:

Logical locking beyond pg_advisory

I need a mechanism of "logical locking" more ductile than the pg_advisory family. I'm thinking of a table ("lock_table") that would be part of the database, with columns * tablename varchar - name of the table "locked" * rowid integer, - id of the row "locked" * ownerid varchar, - identifier of th

Re: check_function_bodies not doing much

I'll take a look at it. Thanks for the recommendation. On Tue, Aug 7, 2018 at 7:22 PM Pavel Stehule wrote: > > > 2018-08-08 0:02 GMT+02:00 Marcelo Lacerda : > >> That's a whole different nightmare that I'm expecting. >> > > > >> >>

Re: check_function_bodies not doing much

#x27;t taken in account for the validation of whether a change to the structure of the database breaks the APIs that the database exposes. On Tue, Aug 7, 2018 at 6:44 PM Merlin Moncure wrote: > On Tue, Aug 7, 2018 at 2:31 PM Tom Lane wrote: > > > > Marcelo Lacerda writes: > >

check_function_bodies not doing much

I was trying to get postgres to warn me that I'm referencing a table that it doesn't exists inside a function so I was told on the IRC to check the setting "check_function_bodies", however when I use it in a plpgsql function it doesn't actually check if the tables in the body exist. Is this the cor

Re: Strange behavior with missing column in SQL function

> CREATE OR REPLACE FUNCTION myfunction(myrow mytable) > RETURNS INTEGER AS $$ > SELECT myrow.c + myrow.b FROM myrow; > $$ LANGUAGE sql; > where "myrow" is a table with a different set of column names from > "mytable". The existing behavior for that is to seek the column name > in "myrow" (th

Strange behavior with missing column in SQL function

Here's the code that reproduces the behavior: http://paste.debian.net/1035412/ I have already discussed this in the IRC channel but there doesn't seem to be a consensus on whether this is a bug here's a brief transcript of RhodiumToad's opinion: > this isn't new, goes back to 9.1 at least > basic

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

For windows platforms only, there is https://www.sqlmanager.net/products/postgresql/manager/ which in Lite version is free. I use it near daily and works like a charm. On 15/07/2018 16:08 , Dmitry Igrishin wrote: Thank you all for your responses! Okay, if I decide to start this project, the Lin

Re: Enforce primary key on every table during dev?

On 02/03/2018 01:10 , Daevor The Devoted wrote: On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: On 02/03/18 06:47, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar mailto:rakeshkumar...@aol.com>

Re: Enforce primary key on every table during dev?

On 01/03/2018 19:05 , Gavin Flower wrote: On 02/03/18 06:47, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote:     >Adding a surrogate key to such a table just adds overhead,     although that could be useful     >in case specifi

Re: Enforce primary key on every table during dev?

On 01/03/2018 18:41 , Adrian Klaver wrote: On 03/01/2018 01:26 PM, Ron Johnson wrote: On 03/01/2018 03:14 PM, Adrian Klaver wrote: On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic. C

Re: Enforce primary key on every table during dev?

On 01/03/2018 17:32 , David G. Johnston wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson >wrote: Why have the overhead of a second unique index?  If it's "ease of joins", then I agree with Francisco Olarte and use the business logic keys in your join

Re: Enforce primary key on every table during dev?

On 01/03/2018 17:21 , Ron Johnson wrote: On 03/01/2018 02:08 PM, marcelo wrote: On 01/03/2018 16:42 , Ron Johnson wrote: On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a synthetic key, then you can insert the same

Re: Enforce primary key on every table during dev?

On 01/03/2018 16:42 , Ron Johnson wrote: On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a synthetic key, then you can insert the same "business data" multiple times with different synthetic keys. -- Angula

Re: Enforce primary key on every table during dev?

On 01/03/2018 16:00 , Ron Johnson wrote: On 03/01/2018 12:32 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson > wrote: On 03/01/2018 11:47 AM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar mailto:

Re: How to know if a database has changed

Hi Sam You are right, and here are the reason behind my question: The server where postgres will be installed is not on 24/7. It turns on in the morning and goes off at the end of the day. The idea is that, as part of the shutdown process, a local backup is made. The next day, that backup wil

pg_dump and logging

When pg_dump runs on a database, is it warranted that the log is fully impacted, or at least, taken into account for the dumping? TIA

How to know if a database has changed

The installation I'm planning will manage several databases, but not all of them will change every day. In order to planning/scripting the pg_dump usage, I would need to know which databases had some change activity at the end of some day. How can it be done? TIA