Re: psql: FATAL: the database system is starting up

On Wed, May 29, 2019 at 10:28 AM Adrian Klaver wrote: > On 5/28/19 6:59 PM, Tom K wrote: > > > > > > On Tue, May 28, 2019 at 9:53 AM Adrian Klaver > > wrote: > > > > > > > Correct. Master election occurs through Patroni. WAL level is set to: > > > > wal_level

Re: Questions about btree_gin vs btree_gist for low cardinality columns

Jeremy's question is *great*, and really well presented. I can't answer his questions, but I am keenly interested in this subject as well. The links he provides lead to some really interesting and well-though-out pieces, well worth reading. I'm going to try restating things in my own way in hopes

Re: json on child table or not

Yes, they are jsonb. Their length varies between 400 and 2.000 chars, when using pg_column_size(jsonb field). When casting to text it goes to 1.800 to 3.500 of length. I didn´t set any storage strategie for that table or field. It´s defined as x for storage. No, I don´t have any index on that field

Re: json on child table or not

How big does the data stored in that field get? More than 2KB? Real question- is it getting stored plain, compressed inline, or toasted? Have you set the storage strategy/type, or is it the "extended" default behavior that compresses and then stores in the toast table if still more than 2000 bytes?

Re: Feature request (or at least discussion): enable autovaccum on temp tables

> > So, a related question, since we have dozens of temp tables and a lot of > code, is there a way to look up what temp tables are being created by the > current session, so I can do a VACUUM or ANALYZE on all of them in bulk? I > know I can inspect pg_temp_* schema, but how to figure out which on

Re: Feature request (or at least discussion): enable autovaccum on temp tables

On Fri, May 31, 2019, 12:26 PM Ivan Voras wrote: > Hi, > On Fri, 31 May 2019 at 17:43, Andrew Gierth > wrote: > >> > "Ivan" == Ivan Voras writes: >> >> Ivan> Since AFAIK temp tables are very close to unlogged ordinary >> Ivan> tables, what would stop this feature from being implemented? >

Re: Inherit Database - Table Permissions

On 5/31/19 11:17 AM, Sathish Kumar wrote: Hi Team, We have a database and keep creating new tables for the requirement. Every time we have to grant readonly permission to the new tables which are created for the db user. Instead is there a way to inherit privileges. Basically, we have a reado

Re: Feature request (or at least discussion): enable autovaccum on temp tables

Hi, On Fri, 31 May 2019 at 17:43, Andrew Gierth wrote: > > "Ivan" == Ivan Voras writes: > > Ivan> Since AFAIK temp tables are very close to unlogged ordinary > Ivan> tables, what would stop this feature from being implemented? > > The key difference between temp tables and other tables is

Re: Feature request (or at least discussion): enable autovaccum on temp tables

hi, On Fri, 31 May 2019 at 17:54, Christopher Browne wrote: > 2. Temp tables are only associated (and visible) in the session in which > you are doing the work. autovacuum operates inside an ordinary session > context, and in a separate connected session, so it can't see your temp > tables; t

Inherit Database - Table Permissions

Hi Team, We have a database and keep creating new tables for the requirement. Every time we have to grant readonly permission to the new tables which are created for the db user. Instead is there a way to inherit privileges. Basically, we have a readonly user, who should run only select statement

Re: Feature request (or at least discussion): enable autovaccum on temp tables

On Fri, 31 May 2019 at 11:26, Ivan Voras wrote: > Hello, > > The reason why we are using temp tables is to allow concurrent runs on > some very large reports which involve creating and heavily churning dozens > of very large tables. > > The problem we're facing is that if we DON'T use temp tables

Re: Feature request (or at least discussion): enable autovaccum on temp tables

> "Ivan" == Ivan Voras writes: Ivan> Since AFAIK temp tables are very close to unlogged ordinary Ivan> tables, what would stop this feature from being implemented? The key difference between temp tables and other tables is that temp table data does not use the shared_buffers but is buffere

Re: Feature request (or at least discussion): enable autovaccum on temp tables

On Fri, 31 May 2019 at 17:38, Adam Brusselback wrote: > Temp tables are not visibile outside of a single connection, so the > autovacuum worker connection isn't able to see it. > I can connect as a superuser via psql while temp tables are being used and I can examine tables in the pg_temp_* sche

Re: Feature request (or at least discussion): enable autovaccum on temp tables

On Fri, 31 May 2019 at 17:34, Pavel Stehule wrote: > Hi > > pá 31. 5. 2019 v 17:26 odesílatel Ivan Voras napsal: > >> Hello, >> >> >> autovacuum check tables once per minute, and working on closed > transactions. Lot of times temporary tables are filled inside some batch, > and immediately some

Re: Feature request (or at least discussion): enable autovaccum on temp tables

Temp tables are not visibile outside of a single connection, so the autovacuum worker connection isn't able to see it. Are you sure that it's actually an issue with accumulating dead tuples, and not an issue with bad statistics? In my processes which are heavy on temp tables, I have to manually r

Re: Feature request (or at least discussion): enable autovaccum on temp tables

Hi pá 31. 5. 2019 v 17:26 odesílatel Ivan Voras napsal: > Hello, > > The reason why we are using temp tables is to allow concurrent runs on > some very large reports which involve creating and heavily churning dozens > of very large tables. > > The problem we're facing is that if we DON'T use te

Feature request (or at least discussion): enable autovaccum on temp tables

Hello, The reason why we are using temp tables is to allow concurrent runs on some very large reports which involve creating and heavily churning dozens of very large tables. The problem we're facing is that if we DON'T use temp tables, the reports finish in a couple of hours. If we DO use temp t

json on child table or not

I have a table with lots of updates in a json field and few updates on other fields. On that table I have several indices and it is main table of other lots of child tables. When querying this table I always do a lateral join with that json field, so having that field on a separate table wouldn´t b

Re: table is hanging

Am 31.05.19 um 14:06 schrieb Saurabh Agrawal: Which query are you trying to run? you can show us also the EXPLAIN - Output. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: table is hanging

Which query are you trying to run? On Fri, May 31, 2019, 16:45 Sonam Sharma wrote: > I only fetched 2 rows and it's responding > > On Fri, May 31, 2019, 4:39 PM Nicklas Avén > wrote: > >> To find out if there is some locking problem or just takes time for the >> client to handle the data, ask j

Re: table is hanging

I only fetched 2 rows and it's responding On Fri, May 31, 2019, 4:39 PM Nicklas Avén wrote: > To find out if there is some locking problem or just takes time for the > client to handle the data, ask just for a small subset of the table. > > > If > > select * from table limit 1; > > works it is p

Re: table is hanging

To find out if there is some locking problem or just takes time for the client to handle the data, ask just for a small subset of the table. If select * from table limit 1; works it is probably just taking a lot of time to send all the data to the client. If the data set includes something

Re: table is hanging

ADding to this, this no of records are present on this table : 19087314 On Fri, May 31, 2019 at 4:28 PM Sonam Sharma wrote: > Hi Team , > > when i am trying to do select on one table , its hanging and not giving > any output. > Can someone please advice what should I check for this ? >

table is hanging

Hi Team , when i am trying to do select on one table , its hanging and not giving any output. Can someone please advice what should I check for this ?

Re: Questions about btree_gin vs btree_gist for low cardinality columns

Will Hartung schrieb am 31.05.2019 um 00:11: > If you have 10M rows with a “STATUS” column of 1 or 2, and an index > on that column, then you have a 2 node index with a bazillion row > pointers. Some systems (I can’t speak to PG in this regard) > degenerate in this kind of use case since the index

Re: Questions about btree_gin vs btree_gist for low cardinality columns

On 2019-05-30 21:00:57 +0200, Peter J. Holzer wrote: > Firstly, the GIN index doesn't generally index single characters. It > uses some rule to split the field into tokens. For example, for a text > field, it might split the field into words (possibly with some > normalization like case-folding and