Replication pull instead of push

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. But then that master server should be reachable and so

Re: Replication pull instead of push

My question was ... how to pull data from Master into Slave. Postgres has a Publication/Subscription model that Slave connects to Master and gets the data. I want something that Master server gets its data which needs to be replicated and send that data to Slave. Is that way possible ? If there i

Replication push instead of pull

We have several servers, our masters, installed on several cities. Then we need have a centralized database where we can do our reports. So, postgres publication/subscription model always opens Master server to Slave gets its data.. There is a way to push data from master to slave instead of pu

single sql, multiple schemas, one result set

Suppose a DB with dozens of schemas with same structure. DB Schema1 Table1 Table2 Schema2 Table1 Table2 Schema3 Table1 Table2 Then we want to execute a SQL on specific schemas and the result of it could be a UNION ALL. So, how could be a function that runs that SQL on

pg_basebackup or dump for starting replication process

For replication purposes only, there are any difference between pg_basebackup or dump to copy data from Master to Slave ? On Docs is written that pg_basebackup can be used both for point-in-time recovery and as the starting point for a log shipping or streaming replication standby servers. We are

Problem with trigger makes Detail record be invalid

I´m using Postgres 10 on ubuntu. suppose a simple Master/Detail structure like this: create table Master(ID integer primary key, name text); create table Detail(ID integer primary key, Master_ID Integer, OtherInfo text); alter table Detail add constraint FKMasterDetail foreign key (Master_ID) ref

Re: Problem with trigger makes Detail record be invalid

I know my trigger is incorrect. I know that I can use TG_OP to know what operation is being done. My question is ... > Is this a bug or it´s mine responsability to check that trigger result ? I think it´s a bug because if something got wrong on detail deletion and it was rolled back, how could be

Re: Problem with trigger makes Detail record be invalid

My point of view that there was a partial rollback, just on detail table. If I´ve done a delete from Master and I have a foreign key to it with cascade option, or all records should be deleted or no one should, this is my point. Did you see that Master table has no records and Detail table has one

Re: Problem with trigger makes Detail record be invalid

Correct, that delete done a partial commit. And this is absolutely unacceptable. I know I did that trigger incorrectly but referential integrity is obligatory. Imagine if I have a database crash and need to restore as soon as possible. How much time I´ll spend removing those records from a backup t

Re: Problem with trigger makes Detail record be invalid

Well, talking about responsabilities, I think one of responsabilities of a mature database is that it can only accept data it was configured for. If you try to store a timestamp in a integer field or a huge numeric value in a smallint field, Postgres will block you because that operation is not acc

Re: execute block like Firebird does

Some time ago I´ve posted this thread because we had lots of these execute blocks to be translated to Postgres. Now, continuing on same matter, I would like to discuss the same topic, basically calling the server one time only, instead of several times. Usually we want get some values from server a

Re: Converting to identity columns with domains on PK columns

Well, I think it´ll not as easy as you said. That tables has dependencies. So, if I try to alter type it gives me ERROR: cannot alter type of a column used in a trigger definition. I dropped all Triggers of that table and it gives me ERROR: cannot alter type of a column used by a view or rule. The

Re: Converting to identity columns with domains on PK columns

Adrian, on Firebird if you create a table or procedure with fields or params declared with domains, they can be used with those domains or with their base type. On Postgres I32 is not equal to integer. create procedure myproc(id i32) returns(x i32) as ... select * from myproc(cast(5 as integer))

Re: Converting to identity columns with domains on PK columns

Sorry, the example I was thinking was this one, which works on Firebird, using its way of writing, obviously. create function myproc(id integer) returns I32 language sql as 'select $1'; On postgres ERROR: return type mismatch in function declared to return i32 What I mean is that Firebird sees I

Re: Converting to identity columns with domains on PK columns

- Because we don´t need to give rigths to user on sequences; - Nobody will change values of pk fields, because we would like to have GENERATE ALWAYS on those PK Fields. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Too slow to create new schema and their tables, functions, triggers.

We have in a single database 190 identical schemas. Now, when we create a new one, with exactly same structure as the previous ones, it takes 20 or 30 minutes to finish. Usual time to finish that script was 30 seconds. Basically, my script creates an entire structure for a new customer: - Create s

Re: Too slow to create new schema and their tables, functions, triggers.

I did not have vacuumed or reindexed my database for last 30 days and that was my problem. It works fine if I do a reindex database before adding that new schema. Well, I´ll try just reindexing system before adding a new schema to see if it works. -- Sent from: https://www.postgresql-archive.

Re: Too slow to create new schema and their tables, functions, triggers.

So, what should I tune on autovacuum ? My script was running strangely. Postgres log shows me the time spent to create functions. That happens when creating triggers and tables too. Sometimes it´s too fast and sometimes ... statement: create or replace function valoresdfe... 0 mins 1.135 secs

Re: Too slow to create new schema and their tables, functions, triggers.

Nope, no one message near those statements. I haven´t changed anything on Postgres.conf related with autovacuum. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Too slow to create new schema and their tables, functions, triggers.

Running that sql: namesetting autovacuum on autovacuum_analyze_scale_factor 0.1 autovacuum_analyze_threshold50 autovacuum_freeze_max_age 2 autovacuum_max_workers 3 autovacuum_multixact_freeze_max_age 4 autovacuum_naptime 60 autovacuum_vacuum_cost_delay

Re: Too slow to create new schema and their tables, functions, triggers.

Nope, that schema and all its entire structure did not exist. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Too slow to create new schema and their tables, functions, triggers.

select count(*), count(*) filter (where last_autovacuum is not null) from pg_stat_all_tables count count 36605 1178 But what tables should I see if vacuum ran on it ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Too slow to create new schema and their tables, functions, triggers.

pg_stat_all_tables count count 37158 807 pg_stat_sys_tables count count 16609 223 pg_stat_user_tables count count 20549 584 -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Too slow to create new schema and their tables, functions, triggers.

Correct, seems to be something wrong on system tables. Maybe our script is running something is already there, we will check. select * from pg_stat_sys_tables where schemaname = 'pg_catalog' relname n_live_tup n_dead_tup pg_attrdef 3699 1095 pg_index

why toasted is created when domain is used ?

select count(*) Total_Tables, count(*) filter (where t2.oid is not null) Toasted_Tables, count(*) filter (where t2.reltuples > 0) Toasted_with_records from pg_class t1 left join pg_class t2 on t1.reltoastrelid = t2.oid and t2.relkind = 't' where t1.relkind = 'r' total_tablestoast

Re: why toasted is created when domain is used ?

Well, I used only domains with fixed sizes. create domain i32 as integer; create domain i16 as smallint; create domain datahora as timestamp; -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

slow queries on system tables

The driver I´m using does some queries on system tables and obviously I cannot change them because are driver inside. These two queries are usually fast, but sometimes they take 8 or 15 times more to run. The question is, can I change something on these tables ? Can i create an index, can I load th

Re: slow queries on system tables

last values for autovacuum and autoanalyse are Null. Thanks, I´ll change and see if that solve our problem -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: slow queries on system tables

Well, not exactly. Values for autovacuum and autoanalyse are Null because I did not configured them for system tables yet, but I´m doing vacuum manually once a week. My question now is why those selects varies that way. Almost all times it spend 20ms but 2 o 3% of the times it spend 500ms, why ?

Re: slow queries on system tables

As I told you before, these queries cannot be changed because the driver creates them. As I cannot change them how can I help Postgres to run it faster, just that. My log_min_duration_statement = 500ms, so I find SQL some dozens of this sql inside it daily, but I´m sure this sql is used thousands

Re: slow queries on system tables

Coming back to my problem, today happened again, that search comes slow. Explaining again, auto vacuum and auto analyse for pg_* tables are not configured one by one, so its using that defaults of scale_factor = 0.2 and threshold = 50. Today, when that problem of speed came back, the view pg_stat_s

Re: slow queries on system tables

https://explain.depesz.com/s/5Rrd EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT current_database() AS TABLE_CATALOG, n.nspname AS TABLE_SCHEMA, t.relname AS TABLE_NAME, t.oid AS TABLE_OID, current_database() AS INDEX_CATALOG, n.nspname AS INDEX_SCHEMA, c.relname AS INDEX_NAME, c.oid AS INDEX_O

Re: slow queries on system tables

autovacuum_analyze_threshold = 50; autovacuum_analyze_scale_factor = 0.1 -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: slow queries on system tables

This week we added just 5 new customers. Every schema has 100 tables, 300 indices, 400 triggers. I cannot imagine our script doing 75000 updates just for adding those schemas. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: slow queries on system tables

these values were collected before vacuum analyse:select (select reltuples from pg_Class where relName = t.relName), * from pg_stat_sys_tables t where schemaname = 'pg_catalog' and relname in ('pg_class','pg_attribute','pg_index'); reltuples relid schemaname relname seq_scanseq

Operator is not unique

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. This error means I have more than one way to calculate that formula ? Did I create that operat

Re: Operator is not unique

I don´t know if I 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 -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

Nope, seems I didn´t create anything. SELECT * FROM pg_operator WHERE oid = '+(numeric, bigint)'::regoperator; ERROR: operator does not exist: +(numeric, bigint) Ok, I can cast, it works. But why works without casting for you and not for me ? -- Sent from: https://www.postgresql-archive.org/Po

Re: Operator is not unique

Doing a inner join with pg_proc I´ll get this result. So, none of this casts I´ve created. Column prorettype is different, this is the problem ?select format_type(castsource, NULL), format_type(casttarget, NULL),castfunc, castcontext, castmethod, pr.* from pg_cast inner join pg_proc pr on castfunc

Re: Operator is not unique

format_type format_type castfunccastcontext castmethod bigint smallint714 a f bigint integer 480 a f bigint real652 i f bigint double precision4

steps of a sql

Some days ago one of my customers claimed that one of his queries were spending too much time. Then on postgres log it appears really strange. That query doesn´t usually appears as parse, sometimes only as execute, because it tooks almost all times less than 500ms. But on that day something happene

Re: Operator is not unique

oprname oprkind oprleft oprright oprresult oprcode + l 0 20 20 int8up + l 0 21 21 int2up + l 0 23 23 int4up + l 0 700

Re: Operator is not unique

SELECT * FROM pg_extension; extname, extversion plpgsql,1.0 pg_stat_statements,1.6 pg_trgm,1.4 tablefunc,1.0 unaccent,1.1 pageinspect,1.7 -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

This select gives me: ERROR: more than one function named "pg_catalog.day_inc" -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Operator is not unique

your select returns no records but if I use WHERE p.proname ~ 'day_inc' instead of WHERE p.proname OPERATOR(pg_catalog.~) '^(pg_catalog.day_inc)$' Schema Name Result data type Argument data types Type pg_catalog day_inc anyelemen

Performance on JSONB select

select * from MyTable where ((JsonBField->>'status'='descartada' and To_Date(JsonBField->'descartada'->>'data','-mm-dd') > Current_Date) or (JsonBField->>'status'='contrato' and To_Date(JsonBField->'contrato'->>'data','-mm-dd') > Current_Date-7) or (JsonBField->>'status'='naoatribui

Re: Performance on JSONB select

ok, my select performed better but I had to create 8 indices to speed up my query. I would love to create just one index using GIN(JsonBField jsonb_ops) but using version 11 I cannot use operators like > and <. I see on docs that version 12 has jsonpath Filter Expression Elements and they inclu

Re: Performance on JSONB select

I think I solved my problem. I didn´t know it was possible but I´ve created an almost dynamic index, because it´ll index for status and corresponding datevalue of an object with that status value. Postgres, I love you. Just one B-Tree index with 2 fields, Status and DateTime of that respective

Re: Performance on JSONB select

I think I solved my problem. I didn´t know it was possible but I´ve created an almost dynamic index, because it´ll index for status and corresponding datevalue of an object with that status value. Postgres, I love you. Just one B-Tree index with 2 fields, Status and DateTime of that respective S

Re: Performance on JSONB select

I told it was almost dynamic because it uses DateValue from an object or another, depending on value of staus key. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Performance on JSONB select

No, no, no, partial index is the where clause of it and I´m not talking about that. I´m talking about datevalue, which will be used one or another, depending on status value This record iindex will be status and datevalue from "visitadescartada" object {   "status": "visitadescartada",   "atribui

Re: Performance on JSONB select

Sorry, I told you that the status was always populated but not, I need that filter for the index because not all records are using that json structure. When json is not null then yes, Status is always there. I have maybe 20 or 25% of records having json populated, so, I really need that filter. Y

Changing PK on replicated database

I have replication using Publication/Subscription and configured with REPLICA IDENTITY DEFAULT, so it uses PK values to do the replication. Then, I´ve imported data in a new schema and that has several tables with a record with its PK = 0. Replication works but my application doesn´t because it nee

Re: Changing PK on replicated database

Correct, those records are on replica too. I´m just talking about the best way to update those keys and all their dependent tables. If I change them first on master they will not be replicated because it will be an update and their pk will not be found on replica, correct ? If so, do I need to upda

Re: Changing PK on replicated database

Very good, records were changed and everything is ok. Well, sorry for this stupid question but some time ago we did something similar to this and our replica stoped, so I was just afraid of that happenning again. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

security on user for replication

We use replication with publication/subsctription. It´s ok, works fine. But if I go to my replica server and do select * from pg_subscription on field subconninfo I have all properties to connect. host, port, user, password and dbname, all these info are available. Documentation says user for repl

naming triggers for execution

On create trigger page of the doc: If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. But suppose we have two triggers, one is called for every table for auditing purposes, for example. And other for a specific process of that tab

Re: naming triggers for execution

well, my way of doing auditing is done on replica, so it´s a little different on production server I do on before update IF (tg_op = 'UPDATE') AND (new.* IS DISTINCT FROM old.*) THEN new.userauditing = User_ID new.datetimeauditing = current_timestamp; END IF; RETURN new; Then, on re

Re: naming triggers for execution

I know it´s possible, but it´s ugly. When you see a trigger called zaudittable is really strange -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

replace single char for string using regexp_replace

I have to replace ; , " and ' chars in an string putting its correspondent ascii value I have a Replace(Replace(Replace(Replace($$Text,with;On'It"$$,',','chr(59)'),';','chr(44)'),'"','chr(34)'),,'chr(39)') It works but I would like to call just one Replace. I tried this but didn´t work select

Options for Postgres FDW

I´m trying to insert/update some tables using FDW. I´m having problems when I want to use default values and triggers. For columns with default values, if I drop that column on foreign table definition is fine for inserts but I cannot use that column for updates/selects because it doesn´t exist o

Re: Options for Postgres FDW

Additionally, there is a list of all options for create foreign server and create foreign table ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

How to discover what table is

I have a publication/subscription replication. Then this week started to see this message on Log of replica server. Message is "duplicate key value violates unique constraint "pksyslookup"" Detail is "Key (lookup_id)=(56) already exists." and on production server message is "logical decoding fou

Re: How to discover what table is

correct, what schema that table belongs to. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: How to discover what table is

Now I have the same problem with a different message. I´ve added a table on all schemas and did a refresh publication. when postgres sees a new table on publisher he goes to replicamand trucates that table to start copying. ok but I have 300 schemas, how can I know what schema that table belongs to

Re: How to discover what table is

built in logical replication -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: How to discover what table is

Well, for now it´s solved but I´ll explain what happens to solve it better on future. Suppose on Master you have a database with hundreds of schemas with same structure, so table Customer happens 500 times on that DB. That database being replicated with publication/subscription for all tables model

Reading WALs

On my built in logical replication I see that sometimes it tooks a long time to be updated with master. The question is how can I see what is being done on replica ? I know I can have a WAL reader, I tried WAL2JSON, it listen all data which comes from master, but as I understood when I use that plu

Re: Reading WALs

pg_Stat_Subscription does not anwer what I want. This view just show if replication is being processed but not exactly what it´s doing. Is it replicating a huge table which an erroneous proess updated several times ? Replication process is recreating what index and that is spending how much time ?

Bug on version 12 ?

select To_Json(Current_Timestamp); returns "2020-05-15T14:49:44.266342+00:00" on version 11.7 or 12.3 So I have lots of JSONS which have timestamp on them. select JS ->> 'mydate'::text, to_timestamp((JS ->> 'mydate'), '-MM-DD HH24:MI:SS') from (select '{"somefield": true, "otherfield":

Re: Bug on version 12 ?

Both are ISO, MDY -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Bug on version 12 ?

Ok Tom but then you cannot go back and forth, like this ... select to_timestamp(jsonb_build_object('mydate', current_timestamp)->>'mydate', '-MM-DD HH24:MI:SS'); works on 11.7 but not on 12.3. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Bug on version 12 ?

I understood, but the problem is that I cannot just migrate from 11 to 12, I have to carefully verify all code before migration. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

Hi Michael, so sorry for the delay. I did never had that problem again, so tougth it was solved, but seems not. We have two different SQL which pushs server to recovery mode. This SQL is the hardest one, because you´ll have to wait some time to get the error. Just runs this SQL, opens your HTOP and

Re: Server goes to Recovery Mode when run a SQL

This is the way you can test to push your server immediatelly to recovery mode. This one you´ll get the problem instantaneously, differently from the last one which you have to wait a long time to happen. A master detail relation with an additional table of configurations. Usually this master has

Re: Server goes to Recovery Mode when run a SQL

Ok, it works if I cast TipoRecebimento to text. (TipoRecebimento::text in (select substring(VarValue from 3) from Var where Name = '/Config/TipoRecebimentoCancelamento')) But now, how can I know what custom operator it´s trying to use, because some day I can get in troubles again because that.

Re: Server goes to Recovery Mode when run a SQL

Correct, on yours it gives an error message, but on mine it tries to execute and goes to recovery. So, how can I know which custom operator is being used on that comparison ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

It works, shouldn´t but works. Results False And I have created these two operators a long time ago. CREATE OPERATOR public.= ( FUNCTION = public.fntextonumero, LEFTARG = text, RIGHTARG = public.i32 ); CREATE OPERATOR public.= ( FUNCTION = public.fntextonumero, LEFTARG = publ

Re: Server goes to Recovery Mode when run a SQL

And the operator function, just to you know, is only a conversion to text CREATE FUNCTION public.fntextonumero(ftext text, finteger public.i32) RETURNS boolean LANGUAGE sql STABLE AS $$ SELECT ftext = finteger::text; $$; CREATE FUNCTION public.fntextonumero(finteger public.i32, ftext te

Re: Server goes to Recovery Mode when run a SQL

Well, I didn´t know where was the problem exactly, then ... The entire script to see that problem is create domain public.i32 as integer; CREATE FUNCTION public.fntextonumero(ftext text, finteger public.i32) RETURNS boolean LANGUAGE sql STABLE AS $$ SELECT ftext = finteger::text; $$; C

Cluster and Vacuum Full

I have tables which are Master Detail and usually my program loads all detail records of a master record. So I configured CLUSTER on all those detail tables to use an index which is the relation with master table. With that I can load less records to Shared Buffers because all detaild records are o

Primary Key index with Include

As you know version 11 gives us the possibility to include aditional columns to an index. So, if I recreate all my primary key indexes to contain a description column, like ... CREATE UNIQUE INDEX CONCURRENTLY Products_pk ON Products(Product_ID) INCLUDE(Name); ALTER TABLE Products ADD CONSTRAINT P

Transition Tables doesn´t have OID

I´m trying to use transition tables for auditing purposes. create trigger MyTableAudit_UPD after update on MyTable referencing old table as Transition_old new table as Transition_new for each statement execute procedure AuditTable(); create or replace function AuditTable() returns trigger languag

Re: Transition Tables doesn´t have OID

yes, mytable has OID column select oid, ID, Name from MyTable limit 3 oid id name 279515 1104Carol 279516 1106Dalva 279517 11008354Melissa -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Transition Tables doesn´t have OID

ok then, help me to find alternatives to it. As I sad, sometimes whe change our PK, so using OID would be a smart way to have a join between old and new transition tables and we would like to use transition tables because each statement is a lot faster than each row for auditing purposes. So, what

Server goes to Recovery Mode when run a SQL

I have a complex query which puts my server in recovery mode every time I run it. I don´t need to say that recovery mode is a situation you don´t want your server goes to.*If I´m using some subselects I´ll get that situation*with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Gr

Re: Server goes to Recovery Mode when run a SQL

I´m using Postgres 10 on ubuntu in a Google VM (8 cores, 32Gb RAM, 250Gb SSD) and DB has 70GB -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

Just to be readable ... And ... server log has only "the database system is in recovery mode" every time I run that query. I have a complex query which puts my server in recovery mode every time I run it. I don´t need to say that recovery mode is a situation you don´t want your server goes to.

Re: Server goes to Recovery Mode when run a SQL

Nothing was change, Postgres 10, Ubuntu 16.04 and Schema was the same, before and after that problem. Well, that database is replicated and on replicated server that problem doesn´t occur. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

About replication ... Logical Replication with CREATE PUBLICATION/SUBSCRIPTION. Yes, some DDL commands were ran on that server but none of them were related with that select. Let me explain better. We have a single server with a single database on it. Each customer has its own schema and connects

Re: Server goes to Recovery Mode when run a SQL

*Well, now we have two queries which stops completelly our postgres server. That problem occurs on 10.6 and 11.1 versions. On both server the problem is the same. Linux logs of old crash are:* Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405788] show_signal_msg: 5 callbacks suppressedFeb 1 18:39

Re: Server goes to Recovery Mode when run a SQL

*Here is a SQL which will get that recovery mode. You can run it on any database because we created it with FROM VALUES, so ...* But this one is that one which grows and grows memory use until all memory and swap space are gone, so problem occurs. That other SQL which gives us the same problem but

Re: Server goes to Recovery Mode when run a SQL

is a sql FROM VALUES, why do you need tables ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

sorry, it´s a missing part of the CTE that constant should be on beginning part of it. with feriados as ( SELECT dia, repete FROM ( VALUES ('2014-10-11'::DATE, FALSE), ('2014-10-13', FALSE), ('2014-10-14', FALSE), ('2014-10-15', FALSE), ('2014-10-16', FALSE), ('2014-10-17', FALS

Refresh Publication takes hours and doesn´t finish

*We use logical replication from a PG version 10.6 to a 11.2. Both are Ubuntu 16.04.We have a hundred schemas with more or less a hundred tables, so number of tables is about 10.000. All replication is ok but when we try to do a REFRESH SUBSCRIPTION because we added a new schema, it takes hours and

PK and FK using Hash index

On version 10 and 11 hash index was redesigned and now seems to be better than btree. Their speed, size and memory consuption are better, so ... Why is not possible to have all PK and FK just using hash indices ? The only thing I need on a PK and FK is search and join, so hash index responds very

Re: Refresh Publication takes hours and doesn´t finish

I tried sometime ago ... but with no responses, I ask you again. pg_publication_tables is a view that is used to refresh publication, but as we have 15.000 tables, it takes hours and doesn´t complete. If I change that view I can have an immediate result. The question is: Can I change that view ? Th

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

I cannot because we created a replication for ALL TABLES -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Refresh Publication takes hours and doesn´t finish

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. I found some people talking that I need to initdb, but initdb means recreate entirely my database or just reinstall my pos

Trigger bug ?

We created a usual trigger which seemed to be not firing or not working properly. Then we put some raise notices on it and saw that it was returning Null values. But why, it´s after insert and is a primary key, cannot be null. create function MyFunction() returns trigger as $$ begin raise '%', n

  1   2   >