Re: [GENERAL] pg_dump crashing

[Thomas Munro]

On Mon, Mar 21, 2016 at 4:18 AM, Matthias Schmitt
 wrote:
> Hello,
>
> sorry for the late response.
>
>> On 15 Mar 2016, at 18:59, rob stone  wrote:
>>
>> I'm running Debian 4.3.5-1 (2016-02-06) x86_64 GNU/Linux.
>>
>> Backups are done via a shell script using double hyphen syntax, as in
>> e.g.:-
>>
>> pg_dump --port=5433 --dbname=mydatabase_1_0_0 --username=mmppostgres
>>  --file=/my_backup_path/mydatabase_1_0_0.dump
>>
>> We do it twice. First with --data_only and the second time with
>>  --format=custom
>>
>> Hasn't failed yet. Don't know if this helps at all but worth a try.
>
> Thank you for your answer. But sorry, no, this does not change anything. Same 
> server crash when executed in a cron job. It runs perfectly when executed 
> manually from the shell.

Is this related?

http://www.postgresql.org/message-id/cak7teys9-o4bterbs3xuk2bffnnd55u2sm9j5r2fi7v6bhj...@mail.gmail.com

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Drop only temporary table

[Aleksander Alekseev]

You can use schema name as a prefix:

postgres=# \d+
   List of relations
  Schema   | Name | Type  |  Owner   |Size| Description 
---+--+---+--++-
 pg_temp_1 | t| table | postgres | 8192 bytes | 
(1 row)

postgres=# drop table pg_temp_1.t;
DROP TABLE

But generally speaking I suggest you generate random names for
temporary tables.

On Fri, 18 Mar 2016 13:47:06 +0100
Durumdara  wrote:

> Dear PG Masters!
> 
> As I experienced I can create normal and temp table with same name.
> 
> create table x (kod integer);
> 
> CREATE TEMPORARY TABLE x (kod integer);
> 
> select tablename from pg_tables where schemaname='public'
>  union all
> select c.relname from pg_class c
> join pg_namespace n on n.oid=c.relnamespace
> where
> n.nspname like 'pg_temp%'
> and c.relkind = 'r'
> and pg_table_is_visible(c.oid);
> 
> ---
> 
> I can see two x tables.
> 
> As I see that drop table stmt don't have "temporary" suboption to
> determine which to need to eliminate - the real or the temporary.
> 
> Same thing would happen with other DDL/DML stmts - what is the
> destination table - the real or the temporary?
> 
> "insert into x(kod) values(1)"
> 
> So what do you think about this problem?
> 
> I want to avoid to remove any real table on resource closing (=
> dropping of temporary table).
> How to I force "drop only temporary"? Prefix, option, etc.
> 
> Thanks for your help!
> 
> dd



-- 
Best regards,
Aleksander Alekseev
http://eax.me/


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump crashing

[Adrian Klaver]

On 03/20/2016 08:24 AM, Matthias Schmitt wrote:
> Hello,
> 
>> On 16 Mar 2016, at 14:55, Adrian Klaver  wrote:
>>
>> On 03/15/2016 08:10 AM, Matthias Schmitt wrote:
>>> Hello,
>>>
>>> since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian 8.3. 
>>> Everything is fine except the daily backup. When calling pg_dump as part of 
>>> a cron job pg_dump crashes:
>>>
>>> 2016-03-15 01:00:02 CETFATAL:  semctl(23232524, 3, SETVAL, 0) failed: 
>>> Invalid argument
>>> 2016-03-15 01:00:02 CETLOG:  server process (PID 22279) exited with exit 
>>> code 1
>>> 2016-03-15 01:00:02 CETLOG:  terminating any other active server processes
>>> 2016-03-15 01:00:02 CETWARNING:  terminating connection because of crash of 
>>> another server process
>>> 2016-03-15 01:00:02 CETDETAIL:  The postmaster has commanded this server 
>>> process to roll back the current transaction and exit, because another 
>>> server process exited abnormally and possibly corrupted shared memory.
>>> 2016-03-15 01:00:02 CETHINT:  In a moment you should be able to reconnect 
>>> to the database and repeat your command.
>>> 2016-03-15 01:00:02 CETLOG:  all server processes terminated; reinitializing
>>> 2016-03-15 01:00:02 CETLOG:  could not remove shared memory segment 
>>> "/PostgreSQL.1804289383": No such file or directory
>>> 2016-03-15 01:00:02 CETLOG:  semctl(22839296, 0, IPC_RMID, ...) failed: 
>>> Invalid argument
>>> 2016-03-15 01:00:02 CETLOG:  semctl(22872065, 0, IPC_RMID, ...) failed: 
>>> Invalid argument
>>> 2016-03-15 01:00:02 CETLOG:  semctl(22904834, 0, IPC_RMID, ...) failed: 
>>> Invalid argument
>>> …
>>>
>>> I am calling pg_dump in my cronjob like this:
>>> su - mmppostgres -c "/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 > 
>>> /my_backup_path/mydatabase_1_0_0.dump"
>>>
>>> After the crash the database runs in recovery mode. A restart of the 
>>> database brings everything back to normal.
>>> This crash is always reproducible and occurs every night during backup. 
>>> When calling the same command via the command line everything run fine. In 
>>> the system log I can see:
>>>
>>
>> Is the command you run via the command line exactly the same, including the 
>> su -?
> 
> Yes.
> 
>> What user are you running the cronjob as?
> 
> root
> 
>> How do you supply the password for the mmppostgres user?
> 
> I configured in pg_hba.conf:
>   
> local  all  mmppostgres  trust
> 
> All local connections from this user are trusted.

So what happens if you either?:

1) In the root crontab, change the command to:

/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 -U mmpostgres > 
/my_backup_path/mydatabase_1_0_0.dump

2) Run the command in 1) in the mmppostgres crontab

> 
> Best regards
> 
> Matthias Schmitt
> 
> magic moving pixel s.a.
> 23, Avenue Grande-Duchesse Charlotte
> L-3441 Dudelange
> Luxembourg
> Phone: +352 54 75 75
> http://www.mmp.lu
> 
> 
> 
> 
> 


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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump crashing

[Matthias Schmitt]

Hello,

> On 16 Mar 2016, at 14:55, Adrian Klaver  wrote:
> 
> On 03/15/2016 08:10 AM, Matthias Schmitt wrote:
>> Hello,
>> 
>> since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian 8.3. 
>> Everything is fine except the daily backup. When calling pg_dump as part of 
>> a cron job pg_dump crashes:
>> 
>> 2016-03-15 01:00:02 CETFATAL:  semctl(23232524, 3, SETVAL, 0) failed: 
>> Invalid argument
>> 2016-03-15 01:00:02 CETLOG:  server process (PID 22279) exited with exit 
>> code 1
>> 2016-03-15 01:00:02 CETLOG:  terminating any other active server processes
>> 2016-03-15 01:00:02 CETWARNING:  terminating connection because of crash of 
>> another server process
>> 2016-03-15 01:00:02 CETDETAIL:  The postmaster has commanded this server 
>> process to roll back the current transaction and exit, because another 
>> server process exited abnormally and possibly corrupted shared memory.
>> 2016-03-15 01:00:02 CETHINT:  In a moment you should be able to reconnect to 
>> the database and repeat your command.
>> 2016-03-15 01:00:02 CETLOG:  all server processes terminated; reinitializing
>> 2016-03-15 01:00:02 CETLOG:  could not remove shared memory segment 
>> "/PostgreSQL.1804289383": No such file or directory
>> 2016-03-15 01:00:02 CETLOG:  semctl(22839296, 0, IPC_RMID, ...) failed: 
>> Invalid argument
>> 2016-03-15 01:00:02 CETLOG:  semctl(22872065, 0, IPC_RMID, ...) failed: 
>> Invalid argument
>> 2016-03-15 01:00:02 CETLOG:  semctl(22904834, 0, IPC_RMID, ...) failed: 
>> Invalid argument
>> …
>> 
>> I am calling pg_dump in my cronjob like this:
>> su - mmppostgres -c "/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 > 
>> /my_backup_path/mydatabase_1_0_0.dump"
>> 
>> After the crash the database runs in recovery mode. A restart of the 
>> database brings everything back to normal.
>> This crash is always reproducible and occurs every night during backup. When 
>> calling the same command via the command line everything run fine. In the 
>> system log I can see:
>> 
> 
> Is the command you run via the command line exactly the same, including the 
> su -?

Yes.

> What user are you running the cronjob as?

root

> How do you supply the password for the mmppostgres user?

I configured in pg_hba.conf:
 
local  all  mmppostgres  trust

All local connections from this user are trusted.

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75
http://www.mmp.lu






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump crashing

[Matthias Schmitt]

Hello,

sorry for the late response.

> On 15 Mar 2016, at 18:59, rob stone  wrote:
> 
> I'm running Debian 4.3.5-1 (2016-02-06) x86_64 GNU/Linux.
> 
> Backups are done via a shell script using double hyphen syntax, as in
> e.g.:-
> 
> pg_dump --port=5433 --dbname=mydatabase_1_0_0 --username=mmppostgres
>  --file=/my_backup_path/mydatabase_1_0_0.dump
> 
> We do it twice. First with --data_only and the second time with
>  --format=custom
> 
> Hasn't failed yet. Don't know if this helps at all but worth a try.

Thank you for your answer. But sorry, no, this does not change anything. Same 
server crash when executed in a cron job. It runs perfectly when executed 
manually from the shell.

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75
http://www.mmp.lu






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Confusing deadlock report

[Thomas Kellerer]

Albe Laurenz schrieb am 16.03.2016 um 14:38:
>>> waits for ShareLock on transaction; blocked by process 24342.
 Process 24342 waits for ShareLock on transaction 39632974; blocked 
 by process 23912.
 Process 23912: UPDATE alpha SET some_flag = $1 WHERE (id = $2)
 Process 24342: INSERT INTO bravo (..., alpha_id) VALUES ($1, $2, 
 $3, $4, $5, $6, $7, $8, $9,
 $10)

 Can the foreign key between bravo and alpha play a role here? With some 
 simple test setups I could not
 get the insert to wait even if it was referencing the row that the other 
 process has updated.

 This happened on 9.3.10 running on Debian
> 
>>> The probable culprit is a foreign key between these tables.
>>>
>>> What foreign keys are defined?
> 
>> The FK in question is:
>>
>>alter table bravo foreign key (alpha_id) references alpha (id);
>>
>> But by simply creating two tables (with a foreign key) and doing an update 
>> in one transaction and the
>> insert in another, I do not get any locks or waiting transactions.
>> (And to be honest: I would have been pretty disappointed if I had)
> 
> Hm, true; I cannot get a lock with these two statements.
> 
> Can you determine what statements were executed in these transactions before 
> the deadlock?
> It was probably one of these that took the conflicting lock.

Unfortunately not. Statement logging is not enabled on that server 
(space-constrained). 

And while we know the statements that can possibly be executed by these parts 
of the application, several on them depend on the actual data, so it's hard to 
tell which path the two transactions actually used. 

Thomas



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Confused by the behavior of pg_basebackup with replication slot

[Yi, Yi]

> Sorry I sent the mail a little too fast. The list is at the end of the
> page.

Thank you for your reply. I saw the list and it helped me a lot :)
 
> On 19/03/2016 15:58, Julien Rouhaud wrote:
> > Hello,
> >
> > On 19/03/2016 15:41, Yi, Yi wrote:
> >> Hello,
> >>
> >> I had an issue with the behavior of pg_basebackup command. I was
> convinced previously that pg_basebackup command always made the binary
> copy of the database cluster files of the postgres master. However, I
> recently noticed that pg_basebackup did not copy the the replication slot
> object of the master, in comparison with the fact that the
> copy-command-based-backup did copy the replication slot object. Is this
> difference designed on purpose ?
> >>
> >
> > Yes.
> >
> >> Considering the difference mentioned above, I'm wandering that is
> there anything else that the pg_basebackup would NOT copy from the
> master ?
> >> In other words, what is the no-copying rules of pg_basebackup ?
> >>
> >
> > The full list is documented here:
> >
> http://www.postgresql.org/docs/current/static/protocol-replication.h
> tml
> >
> 
> Sorry I sent the mail a little too fast. The list is at the end of the
> page.
> 
> > Regards.
> >
> >> Any help will be greatly appreciated. Thanks.
> >>
> >> Best Regards.
> >>
> >
> >
> 
> 
> --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org
> 





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[Michael Paquier]

On Sat, Mar 19, 2016 at 11:42 PM, Robert Haas  wrote:
> On Thu, Mar 10, 2016 at 1:40 AM, David G. Johnston
>  wrote:
>> Adding -hackers for consideration in the Commitfest.
>
> I don't much like how this patch uses the arbitrary constant 50 in no
> fewer than 5 locations.
>
> Also, it seems like we could arrange for head_title to be "" rather
> than NULL when myopt.title is NULL.  Then instead of this:
>
> +if (head_title)
> +snprintf(title, strlen(myopt.title) + 50,
> + _("Watch every %lds\t%s\n%s"),
> + sleep, asctime(localtime()), head_title);
> +else
> +snprintf(title, 50, _("Watch every %lds\t%s"),
> + sleep, asctime(localtime()));
>
> ...we could just the first branch of that if all the time.

OK, why not.

>  if (res == -1)
> +{
> +pg_free(title);
> +pg_free(head_title);
>  return false;
> +}
>
> Instead of repeating the cleanup code, how about making this break;
> then, change the return statement at the bottom of the function to
> return (res != -1).

OK.

And the patch attached gives the following output:
With title:
=# \watch 1
Watch every 1sSun Mar 20 22:28:38 2016
popo
 a
---
 1
(1 row)

And without title:
Watch every 1sSun Mar 20 22:29:31 2016

 a
---
 1
(1 row)
-- 
Michael


psql_watch_title-v3.patch
Description: binary/octet-stream

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Question about shared_buffer cache behavior

[Paul Jones]

In Postgres 9.5.1 with a shared_buffer cache of 7Gb, a SELECT from
a single table that uses an index appears to read the table into the
shared_buffer cache.  Then, as many times as the exact same SELECT is
repeated in the same session, it runs blazingly fast and doesn't even
touch the disk.  All good.

Now, in the *same* session, if a different SELECT from the *same* table,
using the *same* index is run, it appears to read the entire table from
disk again.

Why is this?  Is there something about the query that qualifies the
contents of the share_buffer cache?  Would this act differently for
different kinds of indexes?

PJ


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] spurious /dev/shm related errors on insert

[Michael Charnoky]

On Mar 18, 2016 10:59 AM, "Tom Lane"  wrote:
> Offhand I do not believe that any part of the core PG code would attempt
> to access such a file.  Maybe you've got some extensions in there that
> would do so?

Thanks Tom, thought this was a PG issue. I'll dig more into the guts of the
application

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[Tom Lane]

David Steele  writes:
> On 3/17/16 5:07 PM, David G. Johnston wrote:
>> Figured out it had to be added to 2016-09...done

> Hmm ... this patch is currently marked "needs review" in CF 2016-03.  Am
> I missing something, should this have been closed?

The message I saw was post-1-March.  If it was in fact submitted in
time for 2016-03, then we owe it a review.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

[drum.lu...@gmail.com]

Sorry guys... *WRONG *email

On 17 March 2016 at 14:29, drum.lu...@gmail.com 
wrote:

>
>>>
>> SELECT ​'test_'​ || substring(uuid_generate_v4()::text, 1, 1);
>>
>> Or use a sequence, or just compute "ROW_NUMBER() OVER ()" and tack that
>> on.
>>
>> David J.
>> ​
>>
>>
> Well.. I was able to do it by using:
>
>> INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT billable_id
>> FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%')),
>> INSERT INTO dm.billables_links (mobiuser_id) VALUES ((SELECT id FROM
>> public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE
>> 'Dson%'))
>
>
>
> The problem is that I need to do that at the same time, because of a
> constraint:
>
> ALTER TABLE dm.billables_links
>   ADD CONSTRAINT cc_one_and_only_one_target CHECK ((("customer_id" IS NOT 
> NULL)::integer + ("role_id" IS NOT NULL)::integer + ("mobiuser_id" IS NOT 
> NULL)::integer) = 1);
>
> I'm having trouble by creating that SQL... can anyone help please?
>
> FYI - It has to be in the same transaction because the mobiuser_id must go
> to the selected billable_id on the first select.
>
>

Re: [GENERAL] Error: insufficient data in the message

[George Neuner]

Replying to Adrian because I'm not seeing some of Ranier's posts.

>On 03/18/2016 08:49 AM, Ranier VF wrote:
>
>> Would be possible, example code in C, to format BIGINT
>> param with msvc 32 bits compiler?

>>  > Subject: Re: [GENERAL] Error: insufficient data in the message
>>  > To: ranier_...@hotmail.com
>>  > From: adrian.kla...@aklaver.com
>>  > Date: Fri, 18 Mar 2016 07:50:14 -0700
>>  >
>>  > On 03/18/2016 07:29 AM, Ranier VF wrote:
>>  >
>>  > Ccing list
>>  > > Hi, Thank your for response.
>>  > >
>>  > > After hard time, find this bug.
>>  > > I see that the problem is.
>>  > >
>>  > > length datatypes, in param[2]
>>  > > field is BIGINT (postgresql)
>>  > > param (num_long var) is unsigned long long (32bits)

In 32-bit versions of MSVC, "long long" is 64-bits.  
Also __int64  (with 2 underscores).


>>  > > params[i].data.num_ulong = htonl(params[i].data.num_ulong);

htonl and ntohl do not work on 64-bit values ... you're changing only
the low part.  And I'm suprised that you didn't get a compiler warning
about this.

Windows 8 and higher offer 64-bit versions of these functions: htonll
and ntohll (spelled with an extra L), but these functions are not
available in XP or Win7.

There are portable versions available online or you can easily write
them.  Depending on your platform they both should either reverse the
byte order, or do nothing: TCP's "network" order is big-endian.

The compilers in Visual Studio 2003 and up have the function
_byteswap_uint64  which will do an 8 byte reversal.


>>  > > prep->bindValues[i] = (const uchar *)
>>  > > [i].data.num_ulong;
>>  > > prep->bindLengths[i] = sizeof(ulong);

Should be  sizeof(ulonglong).


>>  > > prep->bindFormats[i] = 1;
>>  > >
>>  > > This fail miserably with windows 32bits (xp, Win7).
>>  > >
>>  > > If change to:
>>  > > prep->bindLengths[i] = 8;
>>  > >
>>  > > Postgresql error goes, but the wrong value is inserted.

Yes.  The length needs to be 8 for a "long long" value, and the value
itself needs to be converted correctly for TCP network byte order.

I don't work directly with libpg, so I can't say if anything else is
wrong here.

Hope this helps,
George



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Insert data in two columns same table

[Vick Khera]

On Wed, Mar 16, 2016 at 9:34 PM, drum.lu...@gmail.com 
wrote:

> The problem is that I need to do that at the same time, because of a
> constraint:
>
>
Mark your constraint as deferrable, and then defer the constraints within
your transaction.

Re: [GENERAL] log temp files are created twice in PL/pgSQL function

[Adrian Klaver]

On 03/16/2016 07:58 AM, Dang Minh Huong wrote:

Hi,

Why does log temp files are created twice when query is executed in PL/pgSQL 
function?
Would you please explain it to me?

As below test result. Log temp files are created twice when SELECT statement is 
put
  into  a PL/pgSQL function. It led a little of performance degradation.
Is there any way to define PL/pgSQL function to avoid this issue?
# I am using PostgreSQL 9.3.9

my test results
-
[postgres@test]$ psql -c "select test_tempfiles();" > /dev/null
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp2223.0", size 3244032
CONTEXT: PL/pgSQL function test_cursor() line 3 at RETURN QUERY
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp23223.1", size 2828062
LOG: duration: 421.426 ms statement: select test_tempfiles();

[postgres@test]$ psql -c "select name from testtbl order by id" > /dev/null
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp25586.0", size 2850816
LOG: duration: 389.054 ms statement: select random from testtbl order by random
-

test_tempfiles() function is defined as below


Are you sure. The query is double quoted which returns an error when run 
on my machine.


What do have logging set to?


-
CREATE OR REPLACE FUNCTION public.test_tempfiles()
RETURNS TABLE(name text)
LANGUAGE plpgsql
AS
$function$
begin
return query execute "select name from testtbl order by id ";
end;
$function$
-

Thanks and best regrards,
bocap













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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general