Re: SQL Query never ending...

[Fabrízio de Royes Mello]

2018-06-20 18:35 GMT-03:00 DiasCosta :
>
> Hi all,
> can someone help me?
>
> I don't know if this is the correct list for this matter. If I'm wrong,
please bear with me and point me in right direction.
>

Here is a good start...



> I have a large query which, largely after more than 24 hours running,
doesn't come to an end;
> However I can see, using system tools, that the postgres process keeps,
although  slowly, reading and writing bytes and the "afinity" dedicated
cores are at 6.25% .
>
> I tried https://www.depesz.com/ but the query was rejected.
>

Are you talking about http://explain.depesz.com ?? If yes you should copy
and paste the EXPLAIN output of your query... If you don't do it yet,
please do it and send to us the results .

And use some external service like pastebin.com to send long SQL statements.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: execute block like Firebird does

[Fabrízio de Royes Mello]

Em dom, 11 de fev de 2018 às 06:47, Andreas Kretschmer <
andr...@a-kretschmer.de> escreveu:

>
>
> Am 11.02.2018 um 06:57 schrieb PegoraroF10:
> > We are migrating our databases from Firebird to PostGres. A useful
> feature
> > Firebird has is Execute Block.
> > What it does is just return a record set from that dynamic SQL, just
> like a
> > PostGres function, but without creating it.
> > It sound like ...
> > execute block returns(ID Integer, Name varchar(50), LastInvoice Date,
> ...)
> > as
> > begin
> >select bla, bla, bla into ...;
> >select bla, bla into ...;
> >suspend;
> > end
> > I know we could create a function but we have several hundred of these
> > blocks running, so ... it would be a huge work to do.
> > So, there is a way to run a dynamic sql which returns a set of records ?
>
> you can use a DO - block:
>
>
> https://www.postgresql.org/docs/current/static/sql-do.html
>

But DO blocks returns "void", I mean you can't return values/records from
this statement.

Regards,
-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Disable TRUST authentication by using ClientAuthentication_hook

[Fabrízio de Royes Mello]

2018-07-13 9:01 GMT-03:00 Guillaume Lelarge :

> 2018-07-13 13:57 GMT+02:00 :
>
>> I’d like to disable the TRUST authentication method for certain servers
>> where modification of pg_hba.conf and restarting a service is fairly easy
>> for a number of users.
>>
>>
>>
>> I looked at this example https://wiki.postgresql.org/im
>> ages/e/e3/Hooks_in_postgresql.pdf It appears that creating a
>> ClientAuthentication_hook and call ereport(ERROR) in case that
>> Port->HbaLine contains TRUST would do the job. Is that right?
>>
>>
>>
>> I am aware that this would not make the server entirely secure but it
>> would make it at least a bit more difficult to enter.
>>
>>
>>
>
> I'm not sure this is such a good idea. You may need the trust
> authentication method, for example if you forgot the superuser password.
> Otherwise, there's good chance you might use the ClientAuthentication hook
> to do what you want.
>
>
>
If you're an server admin you can disable the extension (editing
shared_pre_load_libraries GUC), change password and then enable the
extension again...

And maybe you can implement a simple way to enable/disable this hook inside
the extension.

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Replication pull instead of push

[Fabrízio de Royes Mello]

2018-03-07 16:01 GMT-03:00 PegoraroF10 <mar...@f10.com.br>:
>
> We have several servers, our masters, installed on several cities. Then we
> need have a centralized database (our standby) where we can do our
reports.
> So, usual way of doing postgres replication is creating a
> replication/subscription process.

Ok.


> But then that master server should be reachable and sometimes it´s not,
just
> because their IP changed or because we need to configure its router to get
> Postgres server properly.
>
> Finally my question, there is a way to pull data which needs to be
> replicated from master to slave.
>

The way is fix your network connection and if necessary change your
subscription side do connect again in your server and continue pulling data.

If you don't drop your publication it will retain the WAL until the
subscription connect again and pull data.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: postgres with graph model

[Fabrízio de Royes Mello]

Em qui, 19 de abr de 2018 às 11:54, Philipp Kraus <
philipp.kr...@tu-clausthal.de> escreveu:

> Hello,
>
> I’m using in a project Postgresql and PostGIS for a geospatial data model,
> but now I need also a graph in this structure, so my question is, is there
> any existing
> extension for Postgres to build a graph. I found ltree but this is for
> tree structures only, not for graphs. In general I have different
> undirected weighted graphs. So
> I need some routing algorithms based on the graph weights, distance
> calculation between nodes. My first idea was to use a graph database e.g
> neo4j, but I have
> got a limitation, that the whole system should be designed in Postgres.
> Did you can give some ideas to build a graph within Postgres


Do you already check the pgrouting [1] project?

Regards,

[1] http://pgrouting.org/

> --
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Problem with trigger makes Detail record be invalid

[Fabrízio de Royes Mello]

2018-04-19 15:57 GMT-03:00 Tom Lane <t...@sss.pgh.pa.us>:
>
> Adrian Klaver <adrian.kla...@aklaver.com> writes:
> > On 04/19/2018 10:55 AM, PegoraroF10 wrote:
> >> Is this a bug or it´s mine responsability to check that trigger result
?
>
> > Without seeing exactly what the trigger function on Detail is doing that
> > is not answerable.
>
> I think the OP is complaining because his misimplemented trigger can break
> the consistency of the foreign key constraint.  That is not a bug, it's
> an intentional design decision: triggers are lower-level than foreign key
> enforcement queries, and fire during such queries.  It's easy to construct
> examples where people would be very unhappy if this were not so, because
> then FK-driven updates would not be seen by the table's triggers.  It does
> mean that you have to be careful when writing a trigger.
>

Yeap... it's already mentioned in stackoverflow in ptbr sometime ago [1]
with a reproducible test case.

> (I'm not sure that this issue is adequately documented, though.
> I'd have expected to find something about it in triggers.sgml and/or
> create_trigger.sgml, but in a quick look neither of them mentions foreign
> keys.)
>

We don't have it properly documented... at the time I answered this
question on pt-br stackoverflow I noticed the lack o documentation and
unfortunately I completely forgot to propose a small patch for it.

Regards,


[1]
https://pt.stackoverflow.com/questions/256115/postgresql-foreign-keys-falhando/256398#256398

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Oracle vs PG

[Fabrízio de Royes Mello]

Em ter, 23 de out de 2018 às 17:46, Adrian Klaver 
escreveu:
>
> On 10/23/18 12:58 PM, Ravi Krishna wrote:
> > Well it is Aurora.
> >
> >
https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
> >
>
> Since the article was almost content-free I not would use it on either
> side of the argument. The only thing I pulled from it was Amazon changed
> databases and hit the learning curve. That will happen in either
direction.
>

+1... I completely agree

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Oracle vs PG

[Fabrízio de Royes Mello]

Em ter, 23 de out de 2018 às 17:48, Ravi Krishna 
escreveu:
>
> I agree but this is the key:
>
> "Savepoints are an important database tool for tracking and recovering
individual transactions. On Prime Day, an excessive number of savepoints
was created, and Amazon's Aurora software wasn't able to handle the
pressure, slowing down the overall database performance, the report said."
>

If it's true (I don't know Oracle enough to have a clear opinion) then they
should think better their database transactions design/architecture and not
just move...

And to improve our Savepoint infrastructure we need a more detailed
information.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Logical locking beyond pg_advisory

[Fabrízio de Royes Mello]

Em dom, 16 de set de 2018 às 17:53, marcelo 
escreveu:
>
> 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 the "user" who acquired the lock
> * acquired timestamp - to be able to release "abandoned" locks after a
certain time
>
> and a group of functions
> 1) lock_table (tablename varchar, ownerid varchar) bool - get to lock
over the entire table, setting rowid to zero
> 2) unlock_table (tablename varchar, ownerid varchar) bool - unlock the
table, if the owner is the recorded one
> 3) locked_table (tablename varchar, ownerid varchar) bool - ask if the
table is locked by some user other than the ownerid argument
> 4) lock_row (tablename varchar, rowid integer, ownerid varchar) bool -
similar to pg_try_advisory_lock
> 5) unlock_row (tablename varchar, rowid integer, ownerid varchar) bool -
similar to pg_advisory_unlock
> 6) unlock_all (ownerid varchar) bool - unlock all locks owned by ownerid
>
> The timeout (default, maybe 15 minutes) is implicitly applied if the lock
is taken by another user (there will be no notification).
> Redundant locks are not queued, they simply return true, may be after an
update of the acquired column.
> Successful locks insert a new row, except the rare case of a timeout,
which becomes an update (ownerid and acquired)
> Unlock operations deletes the corresponding row
>
> My question is double
> a) What is the opinion on the project?

Would be nice if you explain more about what kind of problem you want to
solve.

> b) What are the consequences of the large number of inserts and deletions

The first thing came to my mind with this approach is table bloat.

> c) Performance. In fact, pg_advisory* implies a network roundtrip, but (I
think) no table operations.

Yeap... no table operations.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Connection pooling for differing databases?

[Fabrízio de Royes Mello]

Em qui, 7 de mar de 2019 às 16:10, Arjun Ranade 
escreveu:
>
> Hi all,
>
> I'm wondering if there's a tool like pgpool that can provide a single
origin point (host/port) that will proxy/direct connections to the specific
servers that contain the db needing to be accessed.
>
> For example... lets say we had two databases: db1.company.com:5432 and
db2.company.com:5433
>
> Db1 has the database: env1
> Db2 has the database: env2
>
> Is there a tool that will accept connections, so that when users connect
they see there are two databases they can go to: env1 and env2.
>
> If they choose to connect to the env1 db, it routes all traffic to
db1.company.com:5432 and if they choose env2 it routes them to
db2.company.com:5433
>
> Of course there would have to be some requirement such as the databases
on any given server cannot have name collisions with database names on
another server, etc.  Is there a way to do something like this?
>

Yeap, pgbouncer do that. See "databases" configuration section [1].

Regards,

[1] https://pgbouncer.github.io/config.html#section-databases

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Table Replication

[Fabrízio de Royes Mello]

Em ter, 29 de jan de 2019 às 10:24, Sathish Kumar 
escreveu:
>
> Hi,
>
> We are trying to replicate few tables from one postgresql server to
another server. We are currently using Postgresql 9.5.x, is there any way
to achieve it without Postgresql upgrade.

Yeap... use pglogical [1] extension.

Regards,

[1] https://www.2ndquadrant.com/en/resources/pglogical/

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: dump and restore ordered by schema or table name

[Fabrízio de Royes Mello]

Em ter, 4 de jun de 2019 às 17:16, PegoraroF10  escreveu:

> Our database has 180 schemas with 100 tables each, with majority of them
> being small tables. Then, when we do a dump or restore we don´t know how
> much time it´ll spend to do that job.
>
> So, there is an option to dump or restore ordered alphabetically ? It could
> be by schema or table, do we have it ?
>
>
Did you check the -l and -L options? It can help you.

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: with and trigger

[Fabrízio de Royes Mello]

Em qua, 29 de mai de 2019 às 08:52, PegoraroF10 
escreveu:
>
> We like to use With to insert, update and return some value to user. But
some
> informations of those related tables are not available on that time, is
that
> a bug ?
>
> with
>   Master(Master_ID) as (insert into Master(Customer_ID, Field2) values(1,
> 'BlaBla') returning Master_ID),
>   Detail as (insert into Detail(Master_ID, Product_ID, ProductValue)
select
> Master_ID, 5, 50 from Master)
> select Master_ID from Master;
>
> This code works but not as expected because we have a trigger which does
not
> see that data yet.
> Suppose a trigger on Detail which needs to find any info from a table
which
> was inserted on this With. That info is not available, like ...
> create function DetailOfDetail() returns trigger() as -- this trigger
> function is before insert on Detail
> begin
>   new.Discount = (select discount from Customer inner join Master
> using(Customer_ID) where Master_ID = new.Master_ID)
> end;
> This trigger will not work because Master record was not inserted yet.
>
> If change it to a DO it would work but we would like that result, so ...
> This trigger is obviously an example, our tables have more complex
> structures but here we want only to understand the way postgres works or
if
> it´s not working properly.
>

To see updated data using a trigger in the same transaction you should
create trigger on AFTER event.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Refresh Publication takes hours and doesn´t finish

[Fabrízio de Royes Mello]

Em ter, 21 de mai de 2019 às 14:17, PegoraroF10 
escreveu:
>
> Restart Postgres means exactly what ? We tried just restart the service
but
> we tried to refresh publication the old view was used because it took
2hours
> and gave us a timeout.
>

As I said before to change system catalog you should set
"allow_system_table_mods=on" and restart PostgreSQL service.

After that you'll able to recreate the "pg_catalog.pg_publication_tables"
system view. (You can use the Tom's suggestion using LATERAL)

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Re: Refresh Publication takes hours and doesn´t finish

[Fabrízio de Royes Mello]

Em ter, 21 de mai de 2019 às 14:41, Tom Lane  escreveu:
>
> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?=  writes:
> > As I said before to change system catalog you should set
> > "allow_system_table_mods=on" and restart PostgreSQL service.
> > After that you'll able to recreate the
"pg_catalog.pg_publication_tables"
> > system view. (You can use the Tom's suggestion using LATERAL)
>
> It's a view, not a table, so I don't think you need
> allow_system_table_mods.  A quick test here says that being
> superuser is enough to do a CREATE OR REPLACE VIEW on it.
>

Interesting, I tried the following commands and got error:

postgres=# SELECT version();
 version

--
 PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

postgres=# SELECT session_user;
 session_user
--
 postgres
(1 row)

postgres=# SHOW allow_system_table_mods ;
 allow_system_table_mods
-
 off
(1 row)

postgres=# CREATE OR REPLACE VIEW pg_catalog.pg_publication_tables AS
postgres-# SELECT
postgres-# P.pubname AS pubname,
postgres-# N.nspname AS schemaname,
postgres-# C.relname AS tablename
postgres-# FROM pg_publication P, pg_class C
postgres-#  JOIN pg_namespace N ON (N.oid = C.relnamespace),
postgres-#  LATERAL pg_get_publication_tables(P.pubname)
postgres-# WHERE C.oid = pg_get_publication_tables.relid;
ERROR:  permission denied: "pg_publication_tables" is a system catalog

But changing "allow_system_table_mods=on" works as expected:

postgres=# SHOW allow_system_table_mods ;
 allow_system_table_mods
-
 on
(1 row)

postgres=# CREATE OR REPLACE VIEW pg_catalog.pg_publication_tables AS
SELECT
P.pubname AS pubname,
N.nspname AS schemaname,
C.relname AS tablename
FROM pg_publication P, pg_class C
 JOIN pg_namespace N ON (N.oid = C.relnamespace),
 LATERAL pg_get_publication_tables(P.pubname)
WHERE C.oid = pg_get_publication_tables.relid;
CREATE VIEW

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Re: Refresh Publication takes hours and doesn´t finish

[Fabrízio de Royes Mello]

Em seg, 20 de mai de 2019 às 18:30, Tom Lane  escreveu:
>
> Hmm ... given that pg_get_publication_tables() shouldn't return any
> duplicate OIDs, it does seem unnecessarily inefficient to put it in
> an IN-subselect condition.  Peter, is there a reason why this isn't
> a straight lateral join?  I get a much saner-looking plan from
>
> FROM pg_publication P, pg_class C
> -JOIN pg_namespace N ON (N.oid = C.relnamespace)
> -   WHERE C.oid IN (SELECT relid FROM
pg_get_publication_tables(P.pubname));
> +JOIN pg_namespace N ON (N.oid = C.relnamespace),
> +LATERAL pg_get_publication_tables(P.pubname)
> +   WHERE C.oid = pg_get_publication_tables.relid;
>

And why not just JOIN direct with pg_publication_rel ?

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: In-depth commercial postgresql training

[Fabrízio de Royes Mello]

Em qui, 27 de jun de 2019 às 10:47, Marcelo Lacerda <
marceloslace...@gmail.com> escreveu:

> 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 legal reasons we can't
> make payments in any currency other than BRL.
>

Hi Marcelo,

Our Brazilian company called Timbira can help you.

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: patch 11.2 to 11.4

[Fabrízio de Royes Mello]

Em qui, 27 de jun de 2019 às 22:01, Prakash Ramakrishnan <
prakash.ramakrishnan...@nielsen.com> escreveu:

> Hi Fabrizo,
>
> After applying the update command getting this error ,
>
> postg...@shacoyuhss001.enterprisenet.org:/home/postgres
> ==> psql
> psql (11.4, server 11.3)
> Type "help" for help.
>
> postgres=#
> postgres=# select version();
>  version
>
> -
>  PostgreSQL 11.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-36), 64-bit
> (1 row)
>
> Please do the needful.
>
>
During a normal "yum update" (if I'm not wrong) PostgreSQL is restarted...
If not you should restart manually.

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: patch 11.2 to 11.4

[Fabrízio de Royes Mello]

Em qui, 27 de jun de 2019 às 05:35, Prakash Ramakrishnan <
prakash.ramakrishnan...@nielsen.com> escreveu:

> Hi Team,
>
> I done the installation using yum poostgresql 11 server method now i need
> to update or apply the patch using repo file how to do this 11.2 to 11.4
> please share me the steps .
>
>
Just run the following command:

yum update postgresql11*

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Operator is not unique

[Fabrízio de Royes Mello]

Em ter, 24 de set de 2019 às 10:35, PegoraroF10 
escreveu:
>
> If I do ...
> select 1::NUMERIC + 1::BIGINT;
>
> I get ...
> [42725] ERROR: operator is not unique: numeric + bigint Hint: Could not
> choose a best candidate operator. You might need to add explicit type
casts.
>

Witch version are you using? I tried it against current master and
everything is ok:

fabrizio=# SELECT 1::NUMERIC + 1::BIGINT;
 ?column?
--
2
(1 row)


> This error means I have more than one way to calculate that formula ?
> Did I create that operator erroneously ?
>

Did you created an operator for it? Why?

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Operator is not unique

[Fabrízio de Royes Mello]

Em ter, 24 de set de 2019 às 10:52, PegoraroF10 
escreveu:
>
> I don´t know if I did.
>

I think you did.

> PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled
> by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
>

Take a look:

postgres=# SELECT version();
 version

--
 PostgreSQL 11.5 (Debian 11.5-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

postgres=# SELECT 1::NUMERIC + 1::BIGINT;
 ?column?
--
2
(1 row)

And looking at the catalog:

postgres=# SELECT pg_typeof(1::NUMERIC + 1::BIGINT);
 pg_typeof
---
 numeric
(1 row)

postgres=# SELECT * FROM pg_operator WHERE oid = '+(numeric,
numeric)'::regoperator;
 oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash |
oprleft | oprright | oprresult | oprcom | oprnegate |   oprcode   | oprrest
| oprjoin
-+--+--+-+-++-+--+---++---+-+-+-
 +   |   11 |   10 | b   | f   | f  |
 1700 | 1700 |  1700 |   1758 | 0 | numeric_add | -   |
-
(1 row)


Please, try it in your environment and let us know.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: POLL: Adding transaction status to default psql prompt

[Fabrízio de Royes Mello]

Em qua, 5 de fev de 2020 às 23:55, Vik Fearing 
escreveu:

>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>

+1

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Date created for tables

[Fabrízio de Royes Mello]

Em seg., 23 de dez. de 2019 às 23:14, Ron 
escreveu:
>
> You all are grossly over-complicating this.
>

Maybe we are really very conservative, but everyone needs to
understand that every single piece of code added to core is our
responsibility to maintain and make sure don't break the whole thing.

I know it is a desired feature but on the other hand we put a lot of effort
to make PostgreSQL very extensible, so IMHO why don't put effort to create
an extension to implement this feature instead of trying repeatedly to get
it into the core without any success.

Using EventTriggers is very easy to get a very first version tracking local
objects and if we need to add shared objects (databases, roles,
tablespaces) we can use hooks and some piece of C code to do the job.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Need auto fail over cluster solution for PostGres

[Fabrízio de Royes Mello]

Em seg., 30 de dez. de 2019 às 13:30, Girish Kumar 
escreveu:
>
> Hello,
>
> I need to setup an auto fail over cluster for Postgres similar to Always
Available Group(AAG) provided by Microsoft for SQL Server. Any suggestions?
Prefer open source solutions(On Ubuntu)
>

Some FOSS options:

https://github.com/zalando/patroni
https://github.com/sorintlab/stolon
https://github.com/ClusterLabs/PAF

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Conditional column filtering with pglogical replication

[Fabrízio de Royes Mello]

Em sex., 23 de out. de 2020 às 10:35, PALAYRET Jacques <
jacques.palay...@meteo.fr> escreveu:
>
> With PgLogical extension, I have tested the possibility of column
filtering (columns) and row filtering (row_filter).
> But is there a way to do a conditional column filtering ?
> I mean a way to filter a column based on a predicate, with pglogical (so
the filtered values won’t arrive on subscribers).
> For example, with a replicated table t(a integer as Primary Key, b
numeric, c integer, d text), I would like to filter the column b when c
between 10 and 20.
>

Hello,

According to the documentation [1] the row_filter is a normal PostgreSQL
expression with the same limitations of CHECK constraints, so you can do
something like: row_filter := 'c between 10 and 20 and b = ?'

Regards,

[1] https://github.com/2ndQuadrant/pglogical#row-filtering

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Join optimization

[Fabrízio de Royes Mello]

Em sáb, 11 de jul de 2020 às 14:20,  escreveu:

> Hi!
>
> Recently on a blogpost [1] I saw that Oracle was able to "optimize" a join
> strategy by completely eliminating access to a table.
>
> Heres the execution on Oracle 18c [2] and PostgreSQL 13 (beta) [3].
>
> Is there a fundamental reason why PG can't do the same?
>

It does... did you see the “never executed” notice on the Postgres explain
output?

Regards,


-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Getting Involved

[Fabrízio de Royes Mello]

Em qua., 17 de mar. de 2021 às 10:27, Πανος Κοροβεσης <
panoskorove...@outlook.com> escreveu:
>
> Hello,
>
> I am a student and i would like to contribute through the GSoc 2021. Do
you have any tips for getting started and solving a few simple issues? I
cant quite how the whole bug fixing process works.
>
> Any help is greatly appreciated!

Hi,

First of all be welcome... the links below can help you to solve all
initial doubts about the GSoC:

https://wiki.postgresql.org/wiki/GSoC
https://wiki.postgresql.org/wiki/GSoC_2021

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Where can I follow the progress of "Pluggable Storage" development?

[Fabrízio de Royes Mello]

Em seg., 13 de dez. de 2021 às 11:35, Jean Baro  escreveu:
>
> Hello there.
>
> I am just an enthusiast of PostgreSQL and would like to get more
information about Pluggable Storage's progress (or not). Please.
>

We already support it since version 12 released in 2019 but we named it as
"Table Access Method" [1].

If you want to track the development activities you should subscribe to the
pgsql-hackers mailing list [2] and also check the commitfest [3]

Regards,

[1] https://www.postgresql.org/docs/current/tableam.html
[2] https://www.postgresql.org/list/pgsql-hackers/
[3] https://commitfest.postgresql.org/

--
Fabrízio Mello

Re: How to explicitly lock and unlock tables in pgsql?

[Fabrízio de Royes Mello]

Em qua., 16 de mar. de 2022 às 17:30, Shaozhong SHI 
escreveu:

> Table locks present a barrier for progressing queries.
>
> How to explicitly lock and unlock tables in pgsql, so that we can
> guarantee the progress of running scripts?
>
> Regards,
>
> David
>

Have a look at https://www.postgresql.org/docs/current/sql-lock.html

-- 
Fabrízio Mello

Consultor
fabri...@timbira.com.br
https://www.timbira.com.br/
[image: facebook] 
[image: twitter] 
[image: linkedin] 
[image: instagram] 

Re: Postgres calendar?

[Fabrízio de Royes Mello]

Em ter., 4 de out. de 2022 às 18:02, Bruce Momjian 
escreveu:
>
> Would people be interesting in subscribing to a Postgres calendar that
> includes dates for minor releases, final minor release dates for major
> versions, commit fests, and even Postgres events?  For example, it could
> include information from:
>
> https://www.postgresql.org/developer/roadmap/
> https://www.postgresql.org/support/versioning/
> https://commitfest.postgresql.org/
> https://www.postgresql.org/about/events/
>
> We could even add information about beta, release candidate, and final
> major releases, though the final release dates are usually not public.
>
> This could be done in Google Calendar, with an exported ICS file, or via
> a dedicated ICS file.  I could even automate it by scraping our website.
>

+1

This information is very useful.

Regards,

--
Fabrízio Mello