Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?

[Morris de Oryx]

After posting, I realized that this is likely a Stupid User Error. I was
mucking around, and did something along the lines of

delete from pg_statistic_ext;

or
delete from pg_stats_ext;

...instead of DROP STATISTICS calls. Would this likely explain what I'm
seeing? If so, bug is in front of keyboard...my understanding is that you
shouldn't mess with the catalog data directly.

On Sun, Nov 15, 2020 at 6:08 PM Tom Lane  wrote:

> Morris de Oryx  writes:
> > And here's the error that I get back:
> > ERROR:  could not find tuple for statistics object 147574.
>
> Can you give a self-contained recipe for triggering this?
>
> regards, tom lane
>

Re: Race condition with restore_command on streaming replica

[Dilip Kumar]

On Thu, Nov 5, 2020 at 1:39 AM Brad Nicholson  wrote:
>
> Hi,
>
> I've recently been seeing a race condition with the restore_command on 
> replicas using streaming replication.
>
> On the primary, we are archiving wal files to s3 compatible storage via 
> pgBackRest. In the recovery.conf section of the postgresql.conf file on the 
> replicas, we define the restore command as follows:
>
> restore_command = '/usr/bin/pgbackrest --config 
> /conf/postgresql/pgbackrest_restore.conf --stanza=formation archive-get %f 
> "%p"'
>
> We have a three member setup - m-0, m-1, m-2. Consider the case where m-0 is 
> the Primary and m-1 and m-2 are replicas connected to the m-0.
>
> When issuing a switchover (via Patroni) from m-0 to m-1, the connection from 
> m-2 to m-0 is terminated. The restore_command on m-2 is run, and it looks for 
> the .history file for the new timeline. If this happens before the history 
> file is created and pushed to the archive, m-2 will look for the next wal 
> file on the existing timeline in the archive. It will never be created as the 
> source has moved on, so this m-2 hangs waiting on that file. The 
> restore_command on the replica looking for this non-existent file is only run 
> once. This seems like an odd state to be in. The replica is waiting on a new 
> file, but it's not actually looking for it. Is this expected, or should the 
> restore_command be polling for that file?

I am not sure how Patroni does it internally,  can you explain the
scenario in more detail?  Suppose you are executing the promote on m-1
and if the promotion is successful it will switch the timeline and it
will create the timeline history file.  Now, once the promotion is
successful if we change the primary_conninfo on the m-2 then it will
restart the walsender and look for the latest .history file which it
should find either from direct streaming or through the
restore_command.  If you are saying that m-2 tried to look for the
history file before m-1 created it then it seems like you change the
primary_conninfo on m-2 before the m-1 promotion got completed.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Re: conflict with recovery when delay is gone

[Radoslav Nedyalkov]

On Sun, Nov 15, 2020 at 12:48 AM Mohamed Wael Khobalatte <
mkhobala...@grubhub.com> wrote:

>
>
> On Sat, Nov 14, 2020 at 2:46 PM Radoslav Nedyalkov 
> wrote:
>
>>
>>
>> On Fri, Nov 13, 2020 at 8:13 PM Radoslav Nedyalkov 
>> wrote:
>>
>>>
>>>
>>> On Fri, Nov 13, 2020 at 7:37 PM Laurenz Albe 
>>> wrote:
>>>
 On Fri, 2020-11-13 at 15:24 +0200, Radoslav Nedyalkov wrote:
 > On a very busy master-standby setup which runs typical olap
 processing -
 > long living , massive writes statements,  we're getting on the
 standby:
 >
 >  ERROR:  canceling statement due to conflict with recovery
 >  FATAL:  terminating connection due to conflict with recovery
 >
 > The weird thing is that cancellations happen usually after standby
 has experienced
 > some huge delay(2h), still not at the allowed maximum(3h). Even
 recently run statements
 > got cancelled when the delay is already at zero.
 >
 > Sometimes the situation got relaxed after an hour or so.
 > Restarting the server instantly helps.
 >
 > It is pg11.8, centos7, hugepages, shared_buffers 196G from 748G.
 >
 > What phenomenon could we be facing?

 Hard to say.  Perhaps an unusual kind of replication conflict?

 What is in "pg_stat_database_conflicts" on the standby server?

>>>
>>> db01=# select * from pg_stat_database_conflicts;
>>>  datid |  datname  | confl_tablespace | confl_lock | confl_snapshot |
>>> confl_bufferpin | confl_deadlock
>>>
>>> ---+---+--+++-+
>>>  13877 | template0 |0 |  0 |  0 |
>>> 0 |  0
>>>  16400 | template1 |0 |  0 |  0 |
>>> 0 |  0
>>>  16402 | postgres  |0 |  0 |  0 |
>>> 0 |  0
>>>  16401 | db01  |0 |  0 | 51 |
>>> 0 |  0
>>> (4 rows)
>>>
>>> On a freshly restarted standby we've just got similar behaviour after a
>>> 2 hours delay and a slow catch-up.
>>> confl_snapshots is 51 and we have exactly the same number cancelled
>>> statements.
>>>
>>>
>> No luck so far. Searching for the explanation i found we fail into the
>> unexplained case when
>> snapshot conflicts happen even hot_standby_feedback is on.
>>
>> Thanks,
>> Rado
>>
>>
>
> Perhaps you have a value set for old_snapshot_threshold? If not, do the
> walreceiver connections drop out?
>

old_snapshot_threshold is -1 on both master and replica.
walreceiver does not drop.

Bi-directional Replica updates

[Job]

Dear Postgresql Community,

I have been using the "vintage" Rubyrep to replicate some tables on multiple 
Postgresql 9.6 databases.
For performance improving, I'd need to upgrade to Postgresql 12 but, since 
sequences are managed differently, Rubyrep does not work since it had no 
updates during last year.

I was looking at a good BDR-Replica for Postgresql 12 over a Wan and, beside 
some commercial solutions, I saw Bucardo.

Before movin on to another Replica software, has someone been able to make 
Rubyrep work on Postgresql 12?
And, if negative, what's your feeling about Bucardo?

Thank you so much, have a great sunday!
Franz

Re: Bi-directional Replica updates

[Josef Šimánek]

ne 15. 11. 2020 v 12:58 odesílatel Job  napsal:
>
> Dear Postgresql Community,
>
> I have been using the "vintage" Rubyrep to replicate some tables on multiple 
> Postgresql 9.6 databases.
> For performance improving, I'd need to upgrade to Postgresql 12 but, since 
> sequences are managed differently, Rubyrep does not work since it had no 
> updates during last year.

Sequence problem should be fixed in this fork
https://github.com/rubyrep/rubyrep/compare/master...StanBright:master.
Anyway the project seems dead (no activity in git repo, issue tracker
and maillist).

> I was looking at a good BDR-Replica for Postgresql 12 over a Wan and, beside 
> some commercial solutions, I saw Bucardo.
>
> Before movin on to another Replica software, has someone been able to make 
> Rubyrep work on Postgresql 12?
> And, if negative, what's your feeling about Bucardo?
>
> Thank you so much, have a great sunday!
> Franz
>
>

Re: I have just downloaded Postgre SQL and "pgadmin 4" doesn't open.

[Ecenur Corlu]

Hello Mr Klaver,

I downloaded it from "
https://www.enterprisedb.com/software-downloads-postgres";
I start it by just clicking on "pgadmin 4" from the start menu. I also
tried starting it by clicking on "run as administrator"

My system is :
Windows 10
Ram 12GB (11.9 GB usable Ram)
Intel(R) Core (TM) i7-4700HQ CPU @ 2.40GHz
x64

Thank you for your reply.
Ece



Adrian Klaver , 15 Kas 2020 Paz, 03:08 tarihinde
şunu yazdı:

> On 11/14/20 3:45 PM, Ecenur Corlu wrote:
> > *I have just downloaded the latest version of PostgreSQL (vs.13.1) and
>
> Where did you download it from?
>
> More comments inline below.
>
> > when I try to open "pgadmin 4", it doesn't open.* It first shows
>
> How are you starting it?
>
> > */windows wait cursor/* and then the wait cursor gets lost. Nothing
> happens.
> >
> > *I also realized that there is no folder created under* C:\Program
> > Files. *like this* >>>"C:\Program Files\pgAdmin 4" I can't find log docs.
> >
> > I have also tried these methods.
> >
> >  1. I have deleted PostgreSQL and re-downloaded it. It didn't make any
> > difference. The same mistake is happening again.
> >  2. I have downloaded Java.
>
> pgAdmin uses Python/JS/CSS/HTML. Java is not a requirement.
>
> >  3. I have looked at "HKEY_CLASSES_ROOT.css\Content Type" to see if
> > "text/css" is written there. And it was in "text/css" format, so
> > there wasn't any mistake there.
> >  4. I deleted "Anaconda" which was downloaded on my computer. I have
> > read that it blocks pgadmin 4 to start.
> >  5. I made Chrome my default browser.
> >  6. I added in "HKEY_CLASSES_ROOT.jz" a value name: "Content Type" in
> > "Reg SZ" type and it's data is "text/javascript" .Previously there
> > was just"default" value name and it's data was "JSFile" Ihaven't
> > deleted this yet.
> >
> >
> > Thank you in advance for your help.
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Restoring database from false update

[Maksim Fomin]

‐‐‐ Original Message ‐‐‐
On Sunday, November 15, 2020 7:27 AM, David G. Johnston 
 wrote:

> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>
>>> plsql -d tsvt
>> psql (12.5)
>> Type "help" for help.
>>
>> tsvt=# \dt+
>> List of relations
>> Schema | Name | Type | Owner | Size | Description
>> +--+---+--+-+-
>> public | test | table | postgres | 0 bytes |
>> (1 row)
>>
>> It should have tables 'trade', 'trade4' and some others.
>
> What about?
>
> \l+ (Letter “el”)
>
> David J.

Well, it gives something:

tsvt=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | 
Tablespace | Description

---+--+--+-+-+---+-++---
-
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | pg_default 
| default administrative con
nection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7809 
kB | pg_default | unmodifiable empty databas
e
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8161 
kB | pg_default | default template for new d
atabases
| | | | | postgres=CTc/postgres | | |
tsvt | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8089 MB | pg_default |
(4 rows)

tsvt is needed database and it has 8089 MB which sounds promising.

Best regards,
Maxim Fomin

Re: Restoring database from false update

[David G. Johnston]

On Sunday, November 15, 2020, Maksim Fomin  wrote:

> ‐‐‐ Original Message ‐‐‐
> On Sunday, November 15, 2020 7:27 AM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>
>>
>> > plsql -d tsvt
>> psql (12.5)
>> Type "help" for help.
>>
>> tsvt=# \dt+
>> List of relations
>> Schema | Name | Type  |  Owner   |  Size   | Description
>> +--+---+--+-+-
>> public | test | table | postgres | 0 bytes |
>> (1 row)
>>
>> It should have tables 'trade', 'trade4' and some others.
>>
>
> What about?
>
> \l+ (Letter “el”)
>
> David J.
>
>
> Well, it gives something:
>
> tsvt=# \l+
> List
> of databases
>Name|  Owner   | Encoding |   Collate   |Ctype|   Access
> privileges   |  Size   | Tablespace |Description
>
> ---+--+--+-+
> -+---+-++---
> 
> -
> postgres  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8
> |   | 7809 kB | pg_default | default administrative con
> nection database
> template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres  +| 7809 kB | pg_default | unmodifiable empty databas
> e
>|  |  | | |
> postgres=CTc/postgres | ||
> template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres  +| 8161 kB | pg_default | default template for new d
> atabases
>|  |  | | |
> postgres=CTc/postgres | ||
> tsvt  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8
> |   | 8089 MB | pg_default |
> (4 rows)
>
> tsvt is needed database and it has 8089 MB which sounds promising.
>

Show search_path;

?

David J.

Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?

[Tom Lane]

Morris de Oryx  writes:
> After posting, I realized that this is likely a Stupid User Error. I was
> mucking around, and did something along the lines of
> delete from pg_statistic_ext;
> or
> delete from pg_stats_ext;

> ...instead of DROP STATISTICS calls. Would this likely explain what I'm
> seeing?

Ah, yeah, it likely would.

If this isn't a throwaway database, what you'd have to do to clear the
errors is to find and remove the now-dangling links to the deleted objects
in pg_depend.

regards, tom lane

Re: New "function tables" in V13 documentation

[Adrian Klaver]

On 11/14/20 8:24 PM, David G. Johnston wrote:
On Fri, Nov 13, 2020 at 1:48 PM Adrian Klaver > wrote:


Which is an indication that for changes of this scope it would be
prudent to create a mock up and have end users see and comment on
before
rolling them out.


There were mockups and people did provide comments.  Do you have any 


Where were they announced and when?


concrete suggestions on what should have been done, or done differently?


Yes my concrete suggestion was a return to previous layout. It was 
rejected out of hand. I still think that the previous layout is a 
significant improvement over the current layout. Namely, you could see 
more and pick out the information quicker. The current setup is as Kevin 
said an amorphous 'wall of text'. Losing the columns was a big mistake, 
they should be returned.




David J.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: I have just downloaded Postgre SQL and "pgadmin 4" doesn't open.

[Adrian Klaver]

On 11/15/20 4:51 AM, Ecenur Corlu wrote:

Hello Mr Klaver,

I downloaded it from 
"https://www.enterprisedb.com/software-downloads-postgres 
"


The above needs an account to access. Is this one of the EDB paid 
versions of Postgres?


The community edition(if that is what yiu want) can be found here:

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

I start it by just clicking on "pgadmin 4" from the start menu. I also 
tried starting it by clicking on "run as administrator"



Have you looked at the Windows system logs to see if there are any 
relevant errors?


FYI, per your looking for the pgAdmin binaries they will be found here:

C:\Program Files\PostgreSQL\13\pgAdmin4



My system is :
Windows 10
Ram 12GB (11.9 GB usable Ram)
Intel(R) Core (TM) i7-4700HQ CPU @ 2.40GHz
x64

Thank you for your reply.
Ece



Adrian Klaver >, 15 Kas 2020 Paz, 03:08 tarihinde 
şunu yazdı:


On 11/14/20 3:45 PM, Ecenur Corlu wrote:
 > *I have just downloaded the latest version of PostgreSQL
(vs.13.1) and

Where did you download it from?

More comments inline below.

 > when I try to open "pgadmin 4", it doesn't open.* It first shows

How are you starting it?

 > */windows wait cursor/* and then the wait cursor gets lost.
Nothing happens.
 >
 > *I also realized that there is no folder created under* C:\Program
 > Files. *like this* >>>"C:\Program Files\pgAdmin 4" I can't find
log docs.
 >
 > I have also tried these methods.
 >
 >  1. I have deleted PostgreSQL and re-downloaded it. It didn't
make any
 >     difference. The same mistake is happening again.
 >  2. I have downloaded Java.

pgAdmin uses Python/JS/CSS/HTML. Java is not a requirement.

 >  3. I have looked at "HKEY_CLASSES_ROOT.css\Content Type" to see if
 >     "text/css" is written there. And it was in "text/css" format, so
 >     there wasn't any mistake there.
 >  4. I deleted "Anaconda" which was downloaded on my computer. I have
 >     read that it blocks pgadmin 4 to start.
 >  5. I made Chrome my default browser.
 >  6. I added in "HKEY_CLASSES_ROOT.jz" a value name: "Content Type" in
 >     "Reg SZ" type and it's data is "text/javascript" .Previously
there
 >     was just"default" value name and it's data was "JSFile" Ihaven't
 >     deleted this yet.
 >
 >
 > Thank you in advance for your help.
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: New "function tables" in V13 documentation

[David G. Johnston]

On Sun, Nov 15, 2020 at 9:39 AM Adrian Klaver 
wrote:

> On 11/14/20 8:24 PM, David G. Johnston wrote:
> > On Fri, Nov 13, 2020 at 1:48 PM Adrian Klaver  > > wrote:
> >
> > Which is an indication that for changes of this scope it would be
> > prudent to create a mock up and have end users see and comment on
> > before
> > rolling them out.
> >
> >
> > There were mockups and people did provide comments.  Do you have any
>
> Where were they announced and when?
>

Starting back in February.

https://www.postgresql.org/message-id/9326.1581457...@sss.pgh.pa.us

The first commit was applied in April.

>
> > concrete suggestions on what should have been done, or done differently?
>
> Yes my concrete suggestion was a return to previous layout.
>

I am referring to the process by which the idea to change the documentation
layout was made public so people could review it (see above).

In hindsight it could have been handled better.  Waiting longer at
different points and making pronouncements on -announce to solicit feedback
from people who don't follow -docs.  That doesn't change the fact that we
need to move forward with a new patch to "fix" things.  If the agreement is
that fix looks a lot like the old format then so be it.  But getting there
by reverting doesn't seem doable.

David J.

Re: New "function tables" in V13 documentation

[Adrian Klaver]

On 11/14/20 8:24 PM, David G. Johnston wrote:
On Fri, Nov 13, 2020 at 1:48 PM Adrian Klaver > wrote:


Which is an indication that for changes of this scope it would be
prudent to create a mock up and have end users see and comment on
before
rolling them out.


There were mockups and people did provide comments.  Do you have any 


Just realized the mockups would have been in the devel version of the 
docs. I will have to admit to not looking at those as I don't usually 
follow that until they become the final release docs. My mistake and I 
will pay more attention in the future.



concrete suggestions on what should have been done, or done differently?

David J.



--
Adrian Klaver
adrian.kla...@aklaver.com

two questions about toast

[Luca Ferrari]

Hi all,
I've a couple of doubts about toast:
1) is a REINDEX DATABASE hitting also toast tables or they need to be
reindexed manually?
2) while executing a query against toasted values I got this debug
message that I don't know what is meaning
DEBUG:  building index "pg_toast_33875_index" on table "pg_toast_33875" serially

The query I was executing was:
SELECT  lower( f::text )  ||  lower( t::text )  FROM crashy_table
WHERE id = '16385'
via a plpgsql PERFORM.

Thanks,
Luca

Re: New "function tables" in V13 documentation

[Adrian Klaver]

On 11/15/20 9:00 AM, David G. Johnston wrote:
On Sun, Nov 15, 2020 at 9:39 AM Adrian Klaver > wrote:


On 11/14/20 8:24 PM, David G. Johnston wrote:
 > On Fri, Nov 13, 2020 at 1:48 PM Adrian Klaver
mailto:adrian.kla...@aklaver.com>
 > >> wrote:
 >
 >     Which is an indication that for changes of this scope it would be
 >     prudent to create a mock up and have end users see and comment on
 >     before
 >     rolling them out.
 >
 >
 > There were mockups and people did provide comments.  Do you have any

Where were they announced and when?


Starting back in February.

https://www.postgresql.org/message-id/9326.1581457...@sss.pgh.pa.us 



The first commit was applied in April.


 > concrete suggestions on what should have been done, or done
differently?

Yes my concrete suggestion was a return to previous layout.


I am referring to the process by which the idea to change the 
documentation layout was made public so people could review it (see above).


In hindsight it could have been handled better.  Waiting longer at 
different points and making pronouncements on -announce to solicit 
feedback from people who don't follow -docs.  That doesn't change the 


Yes and at least one post to --general. It would have motivated me to 
look at the new docs earlier.


fact that we need to move forward with a new patch to "fix" things.  If 
the agreement is that fix looks a lot like the old format then so be 
it.  But getting there by reverting doesn't seem doable.


The method does not matter to me the layout does.



David J.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: two questions about toast

[Adrian Klaver]

On 11/15/20 9:03 AM, Luca Ferrari wrote:

Hi all,
I've a couple of doubts about toast:
1) is a REINDEX DATABASE hitting also toast tables or they need to be
reindexed manually?


https://www.postgresql.org/docs/current/sql-reindex.html

"DATABASE

Recreate all indexes within the current database. Indexes on shared 
system catalogs are also processed. This form of REINDEX cannot be 
executed inside a transaction block.

"

"TABLE

Recreate all indexes of the specified table. If the table has a 
secondary “TOAST” table, that is reindexed as well.

"

Database --> Table --> TOAST table.


2) while executing a query against toasted values I got this debug
message that I don't know what is meaning


This happened when you where doing the REINDEX DATABASE?


DEBUG:  building index "pg_toast_33875_index" on table "pg_toast_33875" serially

The query I was executing was:
SELECT  lower( f::text )  ||  lower( t::text )  FROM crashy_table
WHERE id = '16385'
via a plpgsql PERFORM.

Thanks,
Luca





--
Adrian Klaver
adrian.kla...@aklaver.com

Restoring database from false update

[Maksim Fomin]

‐‐‐ Original Message ‐‐‐
On Sunday, November 15, 2020 4:32 PM, David G. Johnston 
 wrote:

> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>
>> ‐‐‐ Original Message ‐‐‐
>> On Sunday, November 15, 2020 7:27 AM, David G. Johnston 
>>  wrote:
>>
>>> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>>>
> plsql -d tsvt
 psql (12.5)
 Type "help" for help.

 tsvt=# \dt+
 List of relations
 Schema | Name | Type | Owner | Size | Description
 +--+---+--+-+-
 public | test | table | postgres | 0 bytes |
 (1 row)

 It should have tables 'trade', 'trade4' and some others.
>>>
>>> What about?
>>>
>>> \l+ (Letter “el”)
>>>
>>> David J.
>>
>> Well, it gives something:
>>
>> tsvt=# \l+
>> List of databases
>> Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | 
>> Tablespace | Description
>>
>> ---+--+--+-+-+---+-++---
>> -
>> postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | 
>> pg_default | default administrative con
>> nection database
>> template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 
>> 7809 kB | pg_default | unmodifiable empty databas
>> e
>> | | | | | postgres=CTc/postgres | | |
>> template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 
>> 8161 kB | pg_default | default template for new d
>> atabases
>> | | | | | postgres=CTc/postgres | | |
>> tsvt | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8089 MB | pg_default |
>> (4 rows)
>>
>> tsvt is needed database and it has 8089 MB which sounds promising.
>
> Show search_path;
>
> ?
>
> David J.

It has:
tsvt=# Show search_path;
search_path
-
"$user", public
(1 row)

'user' is literally the name of the current linux user.

Best regards,
Maxim Fomin

Re: Restoring database from false update

[David G. Johnston]

On Sunday, November 15, 2020, Maksim Fomin  wrote:

>
>
> ‐‐‐ Original Message ‐‐‐
> On Sunday, November 15, 2020 4:32 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>
>> ‐‐‐ Original Message ‐‐‐
>> On Sunday, November 15, 2020 7:27 AM, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>>
>>>
>>> > plsql -d tsvt
>>> psql (12.5)
>>> Type "help" for help.
>>>
>>> tsvt=# \dt+
>>> List of relations
>>> Schema | Name | Type  |  Owner   |  Size   | Description
>>> +--+---+--+-+-
>>> public | test | table | postgres | 0 bytes |
>>> (1 row)
>>>
>>> It should have tables 'trade', 'trade4' and some others.
>>>
>>
>> What about?
>>
>> \l+ (Letter “el”)
>>
>> David J.
>>
>>
>> Well, it gives something:
>>
>> tsvt=# \l+
>> List
>> of databases
>>Name|  Owner   | Encoding |   Collate   |Ctype|   Access
>> privileges   |  Size   | Tablespace |Description
>>
>> ---+--+--+-+
>> -+---+-++---
>> 
>> -
>> postgres  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8
>> |   | 7809 kB | pg_default | default administrative con
>> nection database
>> template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> =c/postgres  +| 7809 kB | pg_default | unmodifiable empty databas
>> e
>>|  |  | | |
>> postgres=CTc/postgres | ||
>> template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> =c/postgres  +| 8161 kB | pg_default | default template for new d
>> atabases
>>|  |  | | |
>> postgres=CTc/postgres | ||
>> tsvt  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8
>> |   | 8089 MB | pg_default |
>> (4 rows)
>>
>> tsvt is needed database and it has 8089 MB which sounds promising.
>>
>
> Show search_path;
>
> ?
>
> David J.
>
>
> It has:
> tsvt=# Show search_path;
>search_path
> -
> "$user", public
> (1 row)
>
> 'user' is literally the name of the current linux user.
>

How about

\dn

David J.

Restoring database from false update

[Maksim Fomin]

‐‐‐ Original Message ‐‐‐
On Sunday, November 15, 2020 8:23 PM, David G. Johnston 
 wrote:

> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>
>> ‐‐‐ Original Message ‐‐‐
>> On Sunday, November 15, 2020 4:32 PM, David G. Johnston 
>>  wrote:
>>
>>> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>>>
 ‐‐‐ Original Message ‐‐‐
 On Sunday, November 15, 2020 7:27 AM, David G. Johnston 
  wrote:

> On Sunday, November 15, 2020, Maksim Fomin  wrote:
>
>>> plsql -d tsvt
>> psql (12.5)
>> Type "help" for help.
>>
>> tsvt=# \dt+
>> List of relations
>> Schema | Name | Type | Owner | Size | Description
>> +--+---+--+-+-
>> public | test | table | postgres | 0 bytes |
>> (1 row)
>>
>> It should have tables 'trade', 'trade4' and some others.
>
> What about?
>
> \l+ (Letter “el”)
>
> David J.

 Well, it gives something:

 tsvt=# \l+
 List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | 
 Tablespace | Description

 ---+--+--+-+-+---+-++---
 -
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | 
 pg_default | default administrative con
 nection database
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 
 7809 kB | pg_default | unmodifiable empty databas
 e
 | | | | | postgres=CTc/postgres | | |
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 
 8161 kB | pg_default | default template for new d
 atabases
 | | | | | postgres=CTc/postgres | | |
 tsvt | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8089 MB | 
 pg_default |
 (4 rows)

 tsvt is needed database and it has 8089 MB which sounds promising.
>>>
>>> Show search_path;
>>>
>>> ?
>>>
>>> David J.
>>
>> It has:
>> tsvt=# Show search_path;
>> search_path
>> -
>> "$user", public
>> (1 row)
>>
>> 'user' is literally the name of the current linux user.
>
> How about
>
> \dn
>
> David J.

OK, the issue is fixed. I just tried to fix myself (by looking at security and 
user access right doc pages) and screwed the database even more. Then I 
restored the db from backup and it worked. I have no idea why. In previous 
cases I was shutting down postgresql normally (via systemctl stop) as just now, 
why restoring didn't work in previous cases remains a mystery for me. Just for 
the record, this is output from working db:

tsvt=# Show search_path;
search_path
-
"$user", public
(1 row)

tsvt=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | 
Tablespace | Descripti
on
-+--+--+-+-+---+-++-
---
gmc_finance | user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1729 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | pg_default 
| default administrative c
onnection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7809 
kB | pg_default | unmodifiable empty datab
ase
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8161 
kB | pg_default | default template for new
databases
| | | | | postgres=CTc/postgres | | |
tsvt | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8089 MB | pg_default |
user | user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | pg_default |
(6 rows)

tsvt=# \dn
List of schemas
Name | Owner
+--
public | postgres
(1 row)

tsvt=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
++---+---+-+-
public | strana | table | user | 40 kB |
public | tnved | table | user | 6408 kB |
public | trade | table | user | 6011 MB |
public | trade4 | table | user | 2064 MB |
(4 rows)

Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?

[Morris de Oryx]

Thanks, it's my local copy, which I drop and rebuild constantly as part of
my testing cycle. On our deployed servers, my patch and test procedures are
very strict. Locally, I like to experiment and blow things up. Well,
mission accomplished there ;-)

Thanks for taking the time to answer.

On Mon, Nov 16, 2020 at 3:34 AM Tom Lane  wrote:

> Morris de Oryx  writes:
> > After posting, I realized that this is likely a Stupid User Error. I was
> > mucking around, and did something along the lines of
> > delete from pg_statistic_ext;
> > or
> > delete from pg_stats_ext;
>
> > ...instead of DROP STATISTICS calls. Would this likely explain what I'm
> > seeing?
>
> Ah, yeah, it likely would.
>
> If this isn't a throwaway database, what you'd have to do to clear the
> errors is to find and remove the now-dangling links to the deleted objects
> in pg_depend.
>
> regards, tom lane
>

Unable to compile postgres 13.1 on Slackware current x64

[Condor]

Hello,

unable to compile Postgresql 13.0 and 13.1 on Slackware current x64. 
Here is gcc -v:


Reading specs from /usr/lib64/gcc/x86_64-slackware-linux/9.3.0/specs
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-slackware-linux/9.3.0/lto-wrapper
Target: x86_64-slackware-linux
Configured with: ../configure --prefix=/usr --libdir=/usr/lib64 
--mandir=/usr/man --infodir=/usr/info --enable-shared --enable-bootstrap 
--enable-languages=ada,brig,c,c++,d,fortran,go,lto,objc,obj-c++ 
--enable-threads=posix --enable-checking=release --enable-objc-gc 
--with-system-zlib --enable-libstdcxx-dual-abi 
--with-default-libstdcxx-abi=new --disable-libstdcxx-pch 
--disable-libunwind-exceptions --enable-__cxa_atexit --disable-libssp 
--enable-gnu-unique-object --enable-plugin --enable-lto 
--disable-install-libiberty --disable-werror --with-gnu-ld --with-isl 
--verbose --with-arch-directory=amd64 --disable-gtktest 
--enable-clocale=gnu --disable-multilib --target=x86_64-slackware-linux 
--build=x86_64-slackware-linux --host=x86_64-slackware-linux

Thread model: posix
gcc version 9.3.0 (GCC)


ICU version - 68.1, LLVM version - 11.0.0


Here is configure params:
./configure \
  --prefix=/usr/lib$LIBDIRSUFFIX/postgresql/$VER \
  --libdir=/usr/lib$LIBDIRSUFFIX/postgresql/$VER/lib \
  --includedir=/usr/include \
  --sysconfdir=/etc/postgresql \
  --localstatedir=/var/lib/postgresql \
  --with-tcl \
  --with-tclconfig=/usr/lib$LIBDIRSUFFIX \
  --with-perl \
  --with-python \
  --with-openssl \
  --with-libxml \
  --with-libxslt \
  --with-icu \
  --with-llvm \
  --enable-thread-safety \
  --with-system-tzdata=/usr/share/zoneinfo \
  $ARCH-slackware-linux

The error is:

make[2]: Leaving directory '/tmp/postgresql-13.1/src/backend/utils'
make[1]: Leaving directory '/tmp/postgresql-13.1/src/backend'
x86_64-slackware-linux-gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Werror=vla -Wendif-labels 
-Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security 
-fno-strict-aliasing -fwrapv -fexcess-precision=standard 
-Wno-format-truncation -Wno-stringop-truncation -O2 
-I../../../src/include  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o 
collationcmds.o collationcmds.c

collationcmds.c: In function ‘get_icu_language_tag’:
collationcmds.c:467:51: error: ‘TRUE’ undeclared (first use in this 
function); did you mean ‘IS_TRUE’?
  467 |  uloc_toLanguageTag(localename, buf, sizeof(buf), TRUE, 
&status);

  |   ^~~~
  |   IS_TRUE
collationcmds.c:467:51: note: each undeclared identifier is reported 
only once for each function it appears in

make: *** [: collationcmds.o] Error 1

Changing TRUE to IS_TRUE solve the problem and code is compiled but no 
idea is this a real fix.



Best Regards,
Hristo S.