Re: [GENERAL] Can PG replace redis, amqp, s3 in the future?

[Jeff Janes]

On Sun, Apr 30, 2017 at 4:37 AM, Thomas Güttler <
guettl...@thomas-guettler.de> wrote:

> Is is possible that PostgreSQL will replace these building blocks in the
> future?
>
>  - redis (Caching)
>

PostgreSQL has its own caching.  It might not be quite as effective as
redis', but you can us it if you are willing to take those trade offs.

 - rabbitmq (amqp)
>

PostgreSQL has its own system for this, and other ones can be layered on
top of fully transactional tables.
Again, you can use one or the other, depending on your needs, if you are
willing to deal with the trade offs.



>  - s3 (Blob storage)
>

No.  You can certainly use PostgreSQL to store blobs.  But then, you need
to store the PostgreSQL data **someplace**.  If you don't store it in S3,
you have to store it somewhere else.

Cheers,

Jeff

Re: [GENERAL] Compatibility of libpg

[Igor Korot]

John,

On Mon, May 1, 2017 at 9:38 PM, John R Pierce  wrote:
> On 5/1/2017 5:44 PM, Igor Korot wrote:
>>
>>
>> But I want to build from MSVC. I already have a solution for it. All I
>> need is to create a project inside that solution which will build the dll
>> and lib files for me.
>>
>> Or I have to use nmake?
>
>
> pretty sure you need to run the top level config script to generate all the
> right stuff, then you probably can have MSVC run the makefile in the libpq
> directory.   I find it easier to just build the whole server, then just use
> the libpq.dll rather than trying to build pieces seperately, as it really
> doesn't take very long.   i believe there are notes on building with MSVC on
> Windows,
> https://www.postgresql.org/docs/current/static/install-windows-full.html ...
> I see there are instructions for building libpq only,
> https://www.postgresql.org/docs/current/static/install-windows-libpq.html
> but I believe you still need most of the prerequisites as outlined in 17.1.1
> ...   17.1.3 discusses a mkvcbuild.pl which makes a pgsql.sln project(?) for
> Visual Studio

Ok, I tried to build with nmake as suggested by the official documentation.

Two issues:
1. On the build window I got:

[quote]
link.exe -lib @C:\Users\Igor\AppData\Local\Temp\nm4696.tmp
rc.exe /l 0x409 /fo".\Release\libpq.res" libpq-dist.rc
Microsoft (R) Windows (R) Resource Compiler Version 6.1.7600.16385
Copyright (C) Microsoft Corporation.  All rights reserved.

link.exe @C:\Users\Igor\AppData\Local\Temp\nm48F9.tmp
   Creating library .\Release\libpqdll.lib and object .\Release\libpqdll.exp
mt -manifest .\Release\libpq.dll.manifest -outputresource:.\Release\libp
q.dll;2
Microsoft (R) Manifest Tool version 5.2.3790.2076
Copyright (c) Microsoft Corporation 2005.
All rights reserved.

.\Release\libpq.dll.manifest : general error c1010070: Failed to load and parse
the manifest. The system cannot find the file specified.
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A
\bin\mt.EXE"' : return code '0x1f'
Stop.
NMAKE : fatal error U1077: '"c:\Program Files (x86)\Microsoft Visual Studio 10.0
\VC\BIN\nmake.EXE"' : return code '0x2'
Stop.
[/quote]

2. Nevertheless, I did get the libpg.{dll,lib} files.
However, it looks like they are Release mode.

Is there a way to build a Debug version of the libraries?
I'm building my app in Debug mode right now and prefer not to mix the libraries.
Especially since I know Debug and Release version with MSVC link to a
different run-time.

Thank you.


>
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Compatibility of libpg

[John R Pierce]

On 5/1/2017 5:44 PM, Igor Korot wrote:


But I want to build from MSVC. I already have a solution for it. All I 
need is to create a project inside that solution which will build the 
dll and lib files for me.


Or I have to use nmake?


pretty sure you need to run the top level config script to generate all 
the right stuff, then you probably can have MSVC run the makefile in the 
libpq directory.   I find it easier to just build the whole server, then 
just use the libpq.dll rather than trying to build pieces seperately, as 
it really doesn't take very long.   i believe there are notes on 
building with MSVC on Windows, 
https://www.postgresql.org/docs/current/static/install-windows-full.html 
...   I see there are instructions for building libpq only, 
https://www.postgresql.org/docs/current/static/install-windows-libpq.html 
but I believe you still need most of the prerequisites as outlined in 
17.1.1 ...   17.1.3 discusses a mkvcbuild.pl which makes a pgsql.sln 
project(?) for Visual Studio





--
john r pierce, recycling bits in santa cruz



--
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] Compatibility of libpg

[Igor Korot]

Hi, John,



On May 1, 2017 8:00 PM, "John R Pierce"  wrote:

On 5/1/2017 4:28 PM, Igor Korot wrote:

> Like I said, I don't have dll, I downloaded a source files and would
> like to compile
> the code myself from the MSVC.
>
> And I will use libpg calls directly.
>

build the  postgres server, and it will generate the DLL, then link to that
with your own apps.


Trouble is - I don't need the server,  client only.

Server is already built.



static linking to runtime libraries like this is strongly discouraged.


Yes, dynamic linking is planned.

But I want to build from MSVC. I already have a solution for it. All I need
is to create a project inside that solution which will build the dll and
lib files for me.

Or I have to use nmake?

Thank you.




-- 
john r pierce, recycling bits in santa cruz



-- 
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] all serial type was changed to 1

[Adrian Klaver]

On 05/01/2017 05:04 PM, Max Wang wrote:

Hi Adrian,

Thank you.  The strange things is we only use Python do insert/update/delete 
and do not run other SQL command.


The evidence says otherwise, which leads to:

So nothing ever pulls data out of the database with SELECTS?

There is no monitoring software in the mix?

There is no application using the database?





Regards,
Max


--
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] all serial type was changed to 1

[Max Wang]

Hi Adrian,

Thank you.  The strange things is we only use Python do insert/update/delete 
and do not run other SQL command.

Regards,
Max

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, 2 May 2017 9:55 AM
To: Max Wang ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 05/01/2017 04:29 PM, Max Wang wrote:
> Hi Adrian,
>
> I checked and found
>
> is_cycled | f

Hmm.

A possible cause:

test=# insert into serial_test (fld_1) values ('test'); INSERT 0 1 test=# 
insert into serial_test (fld_1) values ('test2'); INSERT 0 1 test=# insert into 
serial_test (fld_1) values ('test3'); INSERT 0 1 test=# select * from 
serial_test_id_seq ; -[ RECORD 1 ]-+ sequence_name | 
serial_test_id_seq
last_value| 3
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 30
is_cycled | f
is_called | t

test=# select setval('serial_test_id_seq', 1, false); -[ RECORD 1 ] setval | 1

*** The above would not show up in the logs unless you had log_statement set to 
'all' in postgresql.conf **

test=# select * from serial_test_id_seq ; -[ RECORD 1 ]-+ 
sequence_name | serial_test_id_seq
last_value| 1
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 0
is_cycled | f
is_called | f

test=# insert into serial_test (fld_1) values ('test3');
ERROR:  duplicate key value violates unique constraint "serial_test_pkey"
DETAIL:  Key (id)=(1) already exists.


>
> Regards,
> Max
>
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Tuesday, 2 May 2017 9:16 AM
> To: Max Wang ; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] all serial type was changed to 1
>
> On 05/01/2017 04:08 PM, Max Wang wrote:
>> Hi Adrian,
>>
>> Only sequences (id) reset to 1.
>
> Then per Amitabh Kant's suggestion take a look at the cycle setting for the 
> sequences.
>
> For sequence named ts_stamp_test_id_seq:
>
> test=# select * from ts_stamp_test_id_seq ; -[ RECORD 1 
> ]-+- sequence_name | ts_stamp_test_id_seq
> last_value| 6
> start_value   | 1
> increment_by  | 1
> max_value | 9223372036854775807
> min_value | 1
> cache_value   | 1
> log_cnt   | 0
> is_cycled | f
> is_called | t
>
>
> You are looking for whether is_cycled = t
>
> Per the docs:
>
> https://www.postgresql.org/docs/9.6/static/sql-createsequence.html
>
> "CYCLE
> NO CYCLE
>
>  The CYCLE option allows the sequence to wrap around when the maxvalue or 
> minvalue has been reached by an ascending or descending sequence 
> respectively. If the limit is reached, the next number generated will be the 
> minvalue or maxvalue, respectively.
>
>  If NO CYCLE is specified, any calls to nextval after the sequence has 
> reached its maximum value will return an error. If neither CYCLE or NO CYCLE 
> are specified, NO CYCLE is the default.
> "
>
>>
>> Regards,
>> Max
>>
>
>
>


--
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] all serial type was changed to 1

[Melvin Davidson]

On Mon, May 1, 2017 at 7:55 PM, Max Wang  wrote:

> Hi Melvin,
>
>
>
> I already reset id to correct value after this happen. This is a
> production database. We could not stop and wait for trouble shooting. I
> manually reset sequence of id to correct value.
>
>
>
> Below is current result:
>
>
>
> sequence_name | xx_id_seq
>
> last_value| 190996
>
> start_value   | 1
>
> increment_by  | 1
>
> max_value | 9223372036854775807
>
> min_value | 1
>
> cache_value   | 1
>
> log_cnt   | 29
>
> is_cycled | f
>
> is_called | t
>
>
>
> Regards,
>
> Max
>
>
>
> *From:* Melvin Davidson [mailto:melvin6...@gmail.com]
> *Sent:* Tuesday, 2 May 2017 9:49 AM
> *To:* Adrian Klaver 
> *Cc:* Max Wang ; Amitabh Kant ;
> pgsql-general@postgresql.org
>
> *Subject:* Re: [GENERAL] all serial type was changed to 1
>
>
>
>
>
>
>
> On Mon, May 1, 2017 at 7:45 PM, Adrian Klaver 
> wrote:
>
> On 05/01/2017 04:36 PM, Max Wang wrote:
>
> Hi Amitabh,
>
> I mean the serial sequence that controls the id value has been set to 1
> for all tables. That's why I got the duplicate key value error when I tried
> to insert the new record to table.
>
>
> So what does the sequence query show?
>
> As an example in psql:
>
> test=# create table serial_test(id serial PRIMARY KEY, fld_1 varchar);
> CREATE TABLE
>
> test=# \d serial_test
>   Table "public.serial_test"
>  Column |   Type|Modifiers
> +---+---
> ---
>  id | integer   | not null default
> nextval('serial_test_id_seq'::regclass)
>  fld_1  | character varying |
> Indexes:
> "serial_test_pkey" PRIMARY KEY, btree (id)
>
> The above shows that the sequence associated with the serial type is:
> 'serial_test_id_seq'
>
> The below shows how to select from that sequence:
>
> test=# select * from serial_test_id_seq ;
> -[ RECORD 1 ]-+
> sequence_name | serial_test_id_seq
> last_value| 1
> start_value   | 1
> increment_by  | 1
> max_value | 9223372036854775807
> min_value | 1
> cache_value   | 1
> log_cnt   | 0
> is_cycled | f
> is_called | f
>
>
> Can you do that on the serial column from one the affected tables and post
> the results here?
>
>
>
>
> Thanks.
>
> Regards,
> Max
>
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Tuesday, 2 May 2017 9:31 AM
> To: Max Wang ; Amitabh Kant 
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] all serial type was changed to 1
>
> On 05/01/2017 04:11 PM, Max Wang wrote:
>
> Hi Amitabh,
>
>
>
> Thank you for suggestion. We did not reach the limit of serial type.
> Some tables only have hundreds of rows.
>
>
> It would helpful if you ran the query I showed in my previous post on one
> the sequences just so we can see.
>
>  From subsequent post of yours:
>
> "Sorry. I mean all tables’ id column were reset to 1."
>
> I thought I understood on this, now I am not sure. Do you mean that the
> actual values in the id column in all the tables have been set to 1 or that
> the serial sequence that controls the id value has been set to 1?
>
>
>
>
> Regards,
>
> Max
>
>
>
>
>
> --
> 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
>
>
>
> *Hmmm, it's beginning to look like someone did a "SELECT setval('seqname',
> 1);"  For every sequence. *
>
> *Is that a possibility?*
>
>
> --
>
> *Melvin Davidson*
>
> *I reserve the right to fantasize.  Whether or not you wish to share my
> fantasy is entirely up to you. *
>


*>I already reset id to correct value after this happen. *
*Then as Adrian or I suggested, someone did a manual*  *"SELECT
setval('seqname', 1);"  For every sequence. *

*Since you have reset to correct values already, I seriously doubt we can
trace this any furthur.*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] all serial type was changed to 1

[Adrian Klaver]

On 05/01/2017 04:55 PM, Max Wang wrote:

Hi Melvin,



I already reset id to correct value after this happen. This is a
production database. We could not stop and wait for trouble shooting. I
manually reset sequence of id to correct value.


I would grep for setval in any of the code you have touching this 
database. Also are you using any third party code?









--
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] Compatibility of libpg

[John R Pierce]

On 5/1/2017 4:28 PM, Igor Korot wrote:

Like I said, I don't have dll, I downloaded a source files and would
like to compile
the code myself from the MSVC.

And I will use libpg calls directly.


build the  postgres server, and it will generate the DLL, then link to 
that with your own apps.


static linking to runtime libraries like this is strongly discouraged.


--
john r pierce, recycling bits in santa cruz



--
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] all serial type was changed to 1

[Max Wang]

Hi Melvin,

I already reset id to correct value after this happen. This is a production 
database. We could not stop and wait for trouble shooting. I manually reset 
sequence of id to correct value.

Below is current result:

sequence_name | xx_id_seq
last_value| 190996
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 29
is_cycled | f
is_called | t

Regards,
Max

From: Melvin Davidson [mailto:melvin6...@gmail.com]
Sent: Tuesday, 2 May 2017 9:49 AM
To: Adrian Klaver 
Cc: Max Wang ; Amitabh Kant ; 
pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1



On Mon, May 1, 2017 at 7:45 PM, Adrian Klaver 
> wrote:
On 05/01/2017 04:36 PM, Max Wang wrote:
Hi Amitabh,

I mean the serial sequence that controls the id value has been set to 1 for all 
tables. That's why I got the duplicate key value error when I tried to insert 
the new record to table.

So what does the sequence query show?

As an example in psql:

test=# create table serial_test(id serial PRIMARY KEY, fld_1 varchar);
CREATE TABLE

test=# \d serial_test
  Table "public.serial_test"
 Column |   Type|Modifiers
+---+--
 id | integer   | not null default 
nextval('serial_test_id_seq'::regclass)
 fld_1  | character varying |
Indexes:
"serial_test_pkey" PRIMARY KEY, btree (id)

The above shows that the sequence associated with the serial type is:
'serial_test_id_seq'

The below shows how to select from that sequence:

test=# select * from serial_test_id_seq ;
-[ RECORD 1 ]-+
sequence_name | serial_test_id_seq
last_value| 1
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 0
is_cycled | f
is_called | f


Can you do that on the serial column from one the affected tables and post the 
results here?



Thanks.

Regards,
Max

-Original Message-
From: Adrian Klaver 
[mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, 2 May 2017 9:31 AM
To: Max Wang >; Amitabh Kant 
>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 05/01/2017 04:11 PM, Max Wang wrote:
Hi Amitabh,



Thank you for suggestion. We did not reach the limit of serial type.
Some tables only have hundreds of rows.

It would helpful if you ran the query I showed in my previous post on one the 
sequences just so we can see.

 From subsequent post of yours:

"Sorry. I mean all tables’ id column were reset to 1."

I thought I understood on this, now I am not sure. Do you mean that the actual 
values in the id column in all the tables have been set to 1 or that the serial 
sequence that controls the id value has been set to 1?



Regards,

Max





--
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

Hmmm, it's beginning to look like someone did a "SELECT setval('seqname', 1);"  
For every sequence.
Is that a possibility?

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you. 
[http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]

Re: [GENERAL] all serial type was changed to 1

[Adrian Klaver]

On 05/01/2017 04:29 PM, Max Wang wrote:

Hi Adrian,

I checked and found

is_cycled | f


Hmm.

A possible cause:

test=# insert into serial_test (fld_1) values ('test');
INSERT 0 1
test=# insert into serial_test (fld_1) values ('test2');
INSERT 0 1
test=# insert into serial_test (fld_1) values ('test3');
INSERT 0 1
test=# select * from serial_test_id_seq ;
-[ RECORD 1 ]-+
sequence_name | serial_test_id_seq
last_value| 3
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 30
is_cycled | f
is_called | t

test=# select setval('serial_test_id_seq', 1, false);
-[ RECORD 1 ]
setval | 1

*** The above would not show up in the logs unless you had log_statement 
set to 'all' in postgresql.conf **


test=# select * from serial_test_id_seq ;
-[ RECORD 1 ]-+
sequence_name | serial_test_id_seq
last_value| 1
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 0
is_cycled | f
is_called | f

test=# insert into serial_test (fld_1) values ('test3');
ERROR:  duplicate key value violates unique constraint "serial_test_pkey"
DETAIL:  Key (id)=(1) already exists.




Regards,
Max

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, 2 May 2017 9:16 AM
To: Max Wang ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 05/01/2017 04:08 PM, Max Wang wrote:

Hi Adrian,

Only sequences (id) reset to 1.


Then per Amitabh Kant's suggestion take a look at the cycle setting for the 
sequences.

For sequence named ts_stamp_test_id_seq:

test=# select * from ts_stamp_test_id_seq ; -[ RECORD 1 
]-+- sequence_name | ts_stamp_test_id_seq
last_value| 6
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 0
is_cycled | f
is_called | t


You are looking for whether is_cycled = t

Per the docs:

https://www.postgresql.org/docs/9.6/static/sql-createsequence.html

"CYCLE
NO CYCLE

 The CYCLE option allows the sequence to wrap around when the maxvalue or 
minvalue has been reached by an ascending or descending sequence respectively. 
If the limit is reached, the next number generated will be the minvalue or 
maxvalue, respectively.

 If NO CYCLE is specified, any calls to nextval after the sequence has 
reached its maximum value will return an error. If neither CYCLE or NO CYCLE 
are specified, NO CYCLE is the default.
"



Regards,
Max








--
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] all serial type was changed to 1

[Melvin Davidson]

On Mon, May 1, 2017 at 7:45 PM, Adrian Klaver 
wrote:

> On 05/01/2017 04:36 PM, Max Wang wrote:
>
>> Hi Amitabh,
>>
>> I mean the serial sequence that controls the id value has been set to 1
>> for all tables. That's why I got the duplicate key value error when I tried
>> to insert the new record to table.
>>
>
> So what does the sequence query show?
>
> As an example in psql:
>
> test=# create table serial_test(id serial PRIMARY KEY, fld_1 varchar);
> CREATE TABLE
>
> test=# \d serial_test
>   Table "public.serial_test"
>  Column |   Type|Modifiers
> +---+---
> ---
>  id | integer   | not null default
> nextval('serial_test_id_seq'::regclass)
>  fld_1  | character varying |
> Indexes:
> "serial_test_pkey" PRIMARY KEY, btree (id)
>
> The above shows that the sequence associated with the serial type is:
> 'serial_test_id_seq'
>
> The below shows how to select from that sequence:
>
> test=# select * from serial_test_id_seq ;
> -[ RECORD 1 ]-+
> sequence_name | serial_test_id_seq
> last_value| 1
> start_value   | 1
> increment_by  | 1
> max_value | 9223372036854775807
> min_value | 1
> cache_value   | 1
> log_cnt   | 0
> is_cycled | f
> is_called | f
>
>
> Can you do that on the serial column from one the affected tables and post
> the results here?
>
>
>
>
>> Thanks.
>>
>> Regards,
>> Max
>>
>> -Original Message-
>> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>> Sent: Tuesday, 2 May 2017 9:31 AM
>> To: Max Wang ; Amitabh Kant 
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] all serial type was changed to 1
>>
>> On 05/01/2017 04:11 PM, Max Wang wrote:
>>
>>> Hi Amitabh,
>>>
>>>
>>>
>>> Thank you for suggestion. We did not reach the limit of serial type.
>>> Some tables only have hundreds of rows.
>>>
>>
>> It would helpful if you ran the query I showed in my previous post on one
>> the sequences just so we can see.
>>
>>  From subsequent post of yours:
>>
>> "Sorry. I mean all tables’ id column were reset to 1."
>>
>> I thought I understood on this, now I am not sure. Do you mean that the
>> actual values in the id column in all the tables have been set to 1 or that
>> the serial sequence that controls the id value has been set to 1?
>>
>>
>>>
>>>
>>> Regards,
>>>
>>> Max
>>>
>>>
>>>
>>
>>
>>
>
> --
> 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
>


*Hmmm, it's beginning to look like someone did a "SELECT setval('seqname',
1);"  For every sequence. *

*Is that a possibility?*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] all serial type was changed to 1

[Adrian Klaver]

On 05/01/2017 04:36 PM, Max Wang wrote:

Hi Amitabh,

I mean the serial sequence that controls the id value has been set to 1 for all 
tables. That's why I got the duplicate key value error when I tried to insert 
the new record to table.


So what does the sequence query show?

As an example in psql:

test=# create table serial_test(id serial PRIMARY KEY, fld_1 varchar);
CREATE TABLE

test=# \d serial_test
  Table "public.serial_test"
 Column |   Type|Modifiers 


+---+--
 id | integer   | not null default 
nextval('serial_test_id_seq'::regclass)

 fld_1  | character varying |
Indexes:
"serial_test_pkey" PRIMARY KEY, btree (id)

The above shows that the sequence associated with the serial type is:
'serial_test_id_seq'

The below shows how to select from that sequence:

test=# select * from serial_test_id_seq ;
-[ RECORD 1 ]-+
sequence_name | serial_test_id_seq
last_value| 1
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 0
is_cycled | f
is_called | f


Can you do that on the serial column from one the affected tables and 
post the results here?





Thanks.

Regards,
Max

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, 2 May 2017 9:31 AM
To: Max Wang ; Amitabh Kant 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 05/01/2017 04:11 PM, Max Wang wrote:

Hi Amitabh,



Thank you for suggestion. We did not reach the limit of serial type.
Some tables only have hundreds of rows.


It would helpful if you ran the query I showed in my previous post on one the 
sequences just so we can see.

 From subsequent post of yours:

"Sorry. I mean all tables’ id column were reset to 1."

I thought I understood on this, now I am not sure. Do you mean that the actual 
values in the id column in all the tables have been set to 1 or that the serial 
sequence that controls the id value has been set to 1?





Regards,

Max









--
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] all serial type was changed to 1

[Max Wang]

Hi Amitabh,

I mean the serial sequence that controls the id value has been set to 1 for all 
tables. That's why I got the duplicate key value error when I tried to insert 
the new record to table.

Thanks.

Regards,
Max

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, 2 May 2017 9:31 AM
To: Max Wang ; Amitabh Kant 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 05/01/2017 04:11 PM, Max Wang wrote:
> Hi Amitabh,
>
>
>
> Thank you for suggestion. We did not reach the limit of serial type.
> Some tables only have hundreds of rows.

It would helpful if you ran the query I showed in my previous post on one the 
sequences just so we can see.

 From subsequent post of yours:

"Sorry. I mean all tables’ id column were reset to 1."

I thought I understood on this, now I am not sure. Do you mean that the actual 
values in the id column in all the tables have been set to 1 or that the serial 
sequence that controls the id value has been set to 1?

>
>
>
> Regards,
>
> Max
>
>



-- 
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] all serial type was changed to 1

[Melvin Davidson]

On Mon, May 1, 2017 at 7:31 PM, Adrian Klaver 
wrote:

> On 05/01/2017 04:11 PM, Max Wang wrote:
>
>> Hi Amitabh,
>>
>>
>>
>> Thank you for suggestion. We did not reach the limit of serial type.
>> Some tables only have hundreds of rows.
>>
>
> It would helpful if you ran the query I showed in my previous post on one
> the sequences just so we can see.
>
> From subsequent post of yours:
>
> "Sorry. I mean all tables’ id column were reset to 1."
>
> I thought I understood on this, now I am not sure. Do you mean that the
> actual values in the id column in all the tables have been set to 1 or that
> the serial sequence that controls the id value has been set to 1?
>
>
>
>>
>>
>> Regards,
>>
>> Max
>>
>>
>>
>
>
> --
> 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
>










*Just to help clarify things, please execute and attach the output from the
following query.SELECT sequence_schema,
sequence_name,  start_value,
maximum_value,  cycle_optionFROM
information_schema.sequences  ORDER BY 1, 2;   *


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] all serial type was changed to 1

[Adrian Klaver]

On 05/01/2017 04:11 PM, Max Wang wrote:

Hi Amitabh,



Thank you for suggestion. We did not reach the limit of serial type.
Some tables only have hundreds of rows.


It would helpful if you ran the query I showed in my previous post on 
one the sequences just so we can see.


From subsequent post of yours:

"Sorry. I mean all tables’ id column were reset to 1."

I thought I understood on this, now I am not sure. Do you mean that the 
actual values in the id column in all the tables have been set to 1 or 
that the serial sequence that controls the id value has been set to 1?






Regards,

Max






--
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] all serial type was changed to 1

[Max Wang]

Hi Adrian,

I checked and found 

is_cycled | f

Regards,
Max

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, 2 May 2017 9:16 AM
To: Max Wang ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 05/01/2017 04:08 PM, Max Wang wrote:
> Hi Adrian,
>
> Only sequences (id) reset to 1.

Then per Amitabh Kant's suggestion take a look at the cycle setting for the 
sequences.

For sequence named ts_stamp_test_id_seq:

test=# select * from ts_stamp_test_id_seq ; -[ RECORD 1 
]-+- sequence_name | ts_stamp_test_id_seq
last_value| 6
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 0
is_cycled | f
is_called | t


You are looking for whether is_cycled = t

Per the docs:

https://www.postgresql.org/docs/9.6/static/sql-createsequence.html

"CYCLE
NO CYCLE

 The CYCLE option allows the sequence to wrap around when the maxvalue or 
minvalue has been reached by an ascending or descending sequence respectively. 
If the limit is reached, the next number generated will be the minvalue or 
maxvalue, respectively.

 If NO CYCLE is specified, any calls to nextval after the sequence has 
reached its maximum value will return an error. If neither CYCLE or NO CYCLE 
are specified, NO CYCLE is the default.
"

>
> Regards,
> Max
>



-- 
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] Compatibility of libpg

[Igor Korot]

Hi, Jihn,

On Mon, May 1, 2017 at 6:33 PM, John R Pierce  wrote:
> On 5/1/2017 3:08 PM, Igor Korot wrote:
>>
>> Also - I want to create a project inside my MSVC 2010 solution and
>> compile.
>> Is there anything I need besides src/include and src/interface/libpg?
>
>
> if you're using .NET stuff like adodb or oledb, you'll want the npgsql .net
> stuff for postgres.
>
> if you're directly calling libpq functions,  libpq.dll and libpq-fe.h should
> be all you need, along with the couple .h files libpq-fe references, afaik,
> thats just pg_config_ext.h and postgres_ext.h

Like I said, I don't have dll, I downloaded a source files and would
like to compile
the code myself from the MSVC.

And I will use libpg calls directly.

Thank you.

>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] all serial type was changed to 1

[Rob Sargent]

Every row?


On 05/01/2017 05:17 PM, Max Wang wrote:


Sorry. I mean all tables’ id column were reset to 1.

Thanks.

*From:*Melvin Davidson [mailto:melvin6...@gmail.com]
*Sent:* Tuesday, 2 May 2017 9:14 AM
*To:* Max Wang 
*Cc:* Adrian Klaver ; 
pgsql-general@postgresql.org

*Subject:* Re: [GENERAL] all serial type was changed to 1

On Mon, May 1, 2017 at 7:08 PM, Max Wang > wrote:


Hi Adrian,

Only sequences (id) reset to 1.

Regards,
Max

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com
]
Sent: Monday, 1 May 2017 11:30 PM
To: Max Wang >;
pgsql-general@postgresql.org 
Subject: Re: [GENERAL] all serial type was changed to 1

On 04/30/2017 10:51 PM, Max Wang wrote:
> Hi All,
>
>
>
> We have a PostgreSQL database. There are 26 tables and we use serial
> type as primary key.  We had a insert error as "duplicate key value
> violates unique constraint, DETAIL:  Key (id)=(1) already
exists." one
> weeks ago. I checked and found all tables' id were reset to 1.

So to be clear:

Every row in each of the 26 tables has an id of 1?

or

Do you mean the sequences where reset to 1?

>
>
>
> I checked database log and did not find any useful information. 
I am

> not sure why this happen. The only script which connect to this
> database is a Python script and only do normal
insert/update/delete actions.
>
>
>
> Please give me some suggestions if you happen to know something
about
> this issue. I appreciate any feedback you might have.
>
>
>
> I am very new to PostgreSQL and this mail list. Please let me
know if
> I did not something wrong.
>
>
>
> Thank you.
>
>
>
> Regards,
>
> Max
>


--
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



*But as Adrian asked, was the sequence reset to 1 for ALL tables 
sequences or just 1?*


--

*Melvin Davidson*
*I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you. *

Re: [GENERAL] all serial type was changed to 1

[Max Wang]

Sorry. I mean all tables’ id column were reset to 1.

Thanks.



From: Melvin Davidson [mailto:melvin6...@gmail.com]
Sent: Tuesday, 2 May 2017 9:14 AM
To: Max Wang 
Cc: Adrian Klaver ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1



On Mon, May 1, 2017 at 7:08 PM, Max Wang 
> wrote:
Hi Adrian,

Only sequences (id) reset to 1.

Regards,
Max

-Original Message-
From: Adrian Klaver 
[mailto:adrian.kla...@aklaver.com]
Sent: Monday, 1 May 2017 11:30 PM
To: Max Wang >; 
pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1
On 04/30/2017 10:51 PM, Max Wang wrote:
> Hi All,
>
>
>
> We have a PostgreSQL database. There are 26 tables and we use serial
> type as primary key.  We had a insert error as "duplicate key value
> violates unique constraint, DETAIL:  Key (id)=(1) already exists." one
> weeks ago. I checked and found all tables' id were reset to 1.

So to be clear:

Every row in each of the 26 tables has an id of 1?

or

Do you mean the sequences where reset to 1?

>
>
>
> I checked database log and did not find any useful information.  I am
> not sure why this happen. The only script which connect to this
> database is a Python script and only do normal insert/update/delete actions.
>
>
>
> Please give me some suggestions if you happen to know something about
> this issue. I appreciate any feedback you might have.
>
>
>
> I am very new to PostgreSQL and this mail list. Please let me know if
> I did not something wrong.
>
>
>
> Thank you.
>
>
>
> Regards,
>
> Max
>


--
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


But as Adrian asked, was the sequence reset to 1 for ALL tables sequences or 
just 1?
--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you. 
[http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]

Re: [GENERAL] all serial type was changed to 1

[Adrian Klaver]

On 05/01/2017 04:08 PM, Max Wang wrote:

Hi Adrian,

Only sequences (id) reset to 1.


Then per Amitabh Kant's suggestion take a look at the cycle setting for 
the sequences.


For sequence named ts_stamp_test_id_seq:

test=# select * from ts_stamp_test_id_seq ;
-[ RECORD 1 ]-+-
sequence_name | ts_stamp_test_id_seq
last_value| 6
start_value   | 1
increment_by  | 1
max_value | 9223372036854775807
min_value | 1
cache_value   | 1
log_cnt   | 0
is_cycled | f
is_called | t


You are looking for whether is_cycled = t

Per the docs:

https://www.postgresql.org/docs/9.6/static/sql-createsequence.html

"CYCLE
NO CYCLE

The CYCLE option allows the sequence to wrap around when the 
maxvalue or minvalue has been reached by an ascending or descending 
sequence respectively. If the limit is reached, the next number 
generated will be the minvalue or maxvalue, respectively.


If NO CYCLE is specified, any calls to nextval after the sequence 
has reached its maximum value will return an error. If neither CYCLE or 
NO CYCLE are specified, NO CYCLE is the default.

"



Regards,
Max





--
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] all serial type was changed to 1

[Melvin Davidson]

On Mon, May 1, 2017 at 7:08 PM, Max Wang  wrote:

> Hi Adrian,
>
> Only sequences (id) reset to 1.
>
> Regards,
> Max
>
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Monday, 1 May 2017 11:30 PM
> To: Max Wang ; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] all serial type was changed to 1
>
> On 04/30/2017 10:51 PM, Max Wang wrote:
> > Hi All,
> >
> >
> >
> > We have a PostgreSQL database. There are 26 tables and we use serial
> > type as primary key.  We had a insert error as "duplicate key value
> > violates unique constraint, DETAIL:  Key (id)=(1) already exists." one
> > weeks ago. I checked and found all tables' id were reset to 1.
>
> So to be clear:
>
> Every row in each of the 26 tables has an id of 1?
>
> or
>
> Do you mean the sequences where reset to 1?
>
> >
> >
> >
> > I checked database log and did not find any useful information.  I am
> > not sure why this happen. The only script which connect to this
> > database is a Python script and only do normal insert/update/delete
> actions.
> >
> >
> >
> > Please give me some suggestions if you happen to know something about
> > this issue. I appreciate any feedback you might have.
> >
> >
> >
> > I am very new to PostgreSQL and this mail list. Please let me know if
> > I did not something wrong.
> >
> >
> >
> > Thank you.
> >
> >
> >
> > Regards,
> >
> > Max
> >
>
>
> --
> 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
>



*But as Adrian asked, was the sequence reset to 1 for ALL tables sequences
or just 1?*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] all serial type was changed to 1

[Max Wang]

Hi Amitabh,

Thank you for suggestion. We did not reach the limit of serial type. Some 
tables only have hundreds of rows.

Regards,
Max

From: Amitabh Kant [mailto:amitabhk...@gmail.com]
Sent: Monday, 1 May 2017 7:58 PM
To: Max Wang 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1



On Mon, May 1, 2017 at 11:21 AM, Max Wang 
> wrote:
Hi All,

We have a PostgreSQL database. There are 26 tables and we use serial type as 
primary key.  We had a insert error as “duplicate key value violates unique 
constraint, DETAIL:  Key (id)=(1) already exists.” one weeks ago. I checked and 
found all tables’ id were reset to 1.

I checked database log and did not find any useful information.  I am not sure 
why this happen. The only script which connect to this database is a Python 
script and only do normal insert/update/delete actions.

Please give me some suggestions if you happen to know something about this 
issue. I appreciate any feedback you might have.

I am very new to PostgreSQL and this mail list. Please let me know if I did not 
something wrong.

Thank you.

Regards,
Max


By any chance, has it to do anything with the Cycle option of sequences:

https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

Re: [GENERAL] all serial type was changed to 1

[Max Wang]

Hi Adrian,

Only sequences (id) reset to 1.

Regards,
Max

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Monday, 1 May 2017 11:30 PM
To: Max Wang ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 04/30/2017 10:51 PM, Max Wang wrote:
> Hi All,
>
>
>
> We have a PostgreSQL database. There are 26 tables and we use serial 
> type as primary key.  We had a insert error as "duplicate key value 
> violates unique constraint, DETAIL:  Key (id)=(1) already exists." one 
> weeks ago. I checked and found all tables' id were reset to 1.

So to be clear:

Every row in each of the 26 tables has an id of 1?

or

Do you mean the sequences where reset to 1?

>
>
>
> I checked database log and did not find any useful information.  I am 
> not sure why this happen. The only script which connect to this 
> database is a Python script and only do normal insert/update/delete actions.
>
>
>
> Please give me some suggestions if you happen to know something about 
> this issue. I appreciate any feedback you might have.
>
>
>
> I am very new to PostgreSQL and this mail list. Please let me know if 
> I did not something wrong.
>
>
>
> Thank you.
>
>
>
> Regards,
>
> Max
>


--
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] all serial type was changed to 1

[Max Wang]

Hi David,

Thanks for suggestion. We use PostgreSQL 9.5. And we did not import or bulk 
loaded data to these tables.

Regards,
Max


-Original Message-
From: David Rowley [mailto:david.row...@2ndquadrant.com] 
Sent: Monday, 1 May 2017 11:05 PM
To: Max Wang 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 1 May 2017 at 17:51, Max Wang  wrote:
> We have a PostgreSQL database. There are 26 tables and we use serial 
> type as primary key.  We had a insert error as “duplicate key value 
> violates unique constraint, DETAIL:  Key (id)=(1) already exists.” one 
> weeks ago. I checked and found all tables’ id were reset to 1.

Sounds like something that might happen if you'd just bulk loaded the data and 
didn't set the sequences.

If you really did use serial types then you could set all these to the max 
value of the column which they belong to.

The following will give you a list of commands to execute:

SELECT 'select setval(''' || c.relname || ''', max(' ||
quote_ident(a.attname) || ')) from ' || d.refobjid::regclass || ';'
FROM pg_depend d
INNER JOIN pg_class c ON d.objid = c.oid INNER JOIN pg_attribute a ON 
a.attrelid = d.refobjid AND a.attnum = d.refobjsubid WHERE c.relkind = 'S' AND 
d.refclassid = 1259;

You may like to check that returns 26 rows as you expect and verify that all 
those sequences do need reset before running the command.

If you're running Postgres 9.6 and using psql, you can execute the above then 
execute \gexec which will execute the previous result set as commands.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
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] Compatibility of libpg

[John R Pierce]

On 5/1/2017 3:08 PM, Igor Korot wrote:

Also - I want to create a project inside my MSVC 2010 solution and compile.
Is there anything I need besides src/include and src/interface/libpg?


if you're using .NET stuff like adodb or oledb, you'll want the npgsql 
.net stuff for postgres.


if you're directly calling libpq functions,  libpq.dll and libpq-fe.h 
should be all you need, along with the couple .h files libpq-fe 
references, afaik, thats just pg_config_ext.h and postgres_ext.h



--
john r pierce, recycling bits in santa cruz



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

[GENERAL] Compatibility of libpg

[Igor Korot]

Hi,
I just downloaded a latest (9.6.2) version of libpg.

I am wondering - what is the lowest version of PostgreSQL it will be able
to connect?

Also - I want to create a project inside my MSVC 2010 solution and compile.
Is there anything I need besides src/include and src/interface/libpg?

Thank you.


-- 
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] Can PG replace redis, amqp, s3 in the future?

[Bill Moran]

On Mon, 1 May 2017 22:05:03 +0200
Thomas Güttler  wrote:
> > 
> > There's a well-written article I saw recently that directly addresses
> > your question ... I'm too lazy to find it, but google will probably
> > turn it up for you.
> 
> I tried to find it, but failed. Can you give me some keywords to find
> this well-written article?

I can't seem find it again. Sorry.

-- 
Bill Moran 


-- 
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] Can PG replace redis, amqp, s3 in the future?

[Bill Moran]

On Mon, 1 May 2017 22:02:15 +0200
Thomas Güttler  wrote:
> Leaving two things open:
> 
>  - blob storage
>  - redis/caching

I've used Postgres for both of these purposes, and at the load
level we were experiencing at the time, it worked fine.

We later implemented Redis when our caching requirements exceeded
what Postgres could do in that capacity. We never switched to
anything else for blob storage, as Postgres was always sufficient.

-- 
Bill Moran 


-- 
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] Can PG replace redis, amqp, s3 in the future?

[Sven R. Kunze]

On 30.04.2017 16:25, Steve Atkins wrote:

You can use postgresql for caching, but caches don't require the data
durability that a database offers, and can be implemented much more
efficiently.


I for one can understand Thomas' need for a single solution.
Just recently I needed a cache which was supposed to be set up in a 
SERIALIZABLE manner as in 
https://www.postgresql.org/docs/devel/static/transaction-iso.html#xact-serializable 
Available cache mechanisms would have produce erroneous results. So, I 
went for PG.


But it's still a cache, isn't it?


You can use postgresql to provide message queue services and it
does so reasonably well, particularly when the messages are generated within
the database. But it's not going to do so as efficiently, or be as easy to
monitor, to make highly redundant or to scale across a whole datacenter
as a dedicated message queue service.

You could use postgresql to store binary blobs, but it'd be a horrifically
inefficient way to do it. (Using postgresql to store the metadata, while
the content is stored elsewhere, sure).

Use the right tool for the job.


I think it's not as easy as ads and buzz words make us believe it is.

Especially when it comes to reinventing the wheel, I prefer a single 
solution. With the better JSON support, PG made NoSQL obsolete. I don't 
see why this cannot happen with blob storage and massive scale out. Just 
a matter of time, if you ask me.


Regards,
Sven

Re: [GENERAL] Can PG replace redis, amqp, s3 in the future?

[Thomas Güttler]

Am 30.04.2017 um 17:09 schrieb Bill Moran:
> On Sun, 30 Apr 2017 13:37:02 +0200
> Thomas Güttler  wrote:
> 
>> Is is possible that PostgreSQL will replace these building blocks in the 
>> future?
>>  
>>  - redis (Caching)
>>  - rabbitmq (amqp)
>>  - s3 (Blob storage)
>>
>> One question is "is it possible?", then next "is it feasible?"
>>
>> I think it would be great if I could use PG only and if I could
>> avoid the other types of servers.
>>
>> The benefit is not very obvious on the first sight. I think it will saves you
>> time, money and energy only in the long run.
>>
>> What do you think?
> 
> There's a well-written article I saw recently that directly addresses
> your question ... I'm too lazy to find it, but google will probably
> turn it up for you.
> 

I tried to find it, but failed. Can you give me some keywords to find
this well-written article?



> Take a message bus for example. PG's notify works pretty damn well as a
> centralized message bus. But if you need a distributed message bus or you
> need massive throughput, you're almost certainly better of with something
> specifically designed for that purpose.

SELECT FOR UPDATE ... SKIP LOCKED looks nice:

 https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/




> Of course, if you need structured, relational data to be stored reliably,
> you can't do much better than Postgres.

Yes, PG is our solid central data storage.

Regards,
 Thomas Güttler


-- 
I am looking for feedback for my personal programming guidelines:
https://github.com/guettli/programming-guidelines


-- 
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] Can PG replace redis, amqp, s3 in the future?

[Thomas Güttler]

Am 30.04.2017 um 15:39 schrieb Thomas Delrue:
> On April 30, 2017 1:37:02 PM GMT+02:00, "Thomas Güttler" 
>  wrote:
>> Is is possible that PostgreSQL will replace these building blocks in
>> the future?
>>
>> - redis (Caching)
>> - rabbitmq (amqp)
>> - s3 (Blob storage)
> 
> These are three very different sets of functionalities, each requiring a 
> different approach.  I am curious as to why you are thinking about having a 
> single piece of software that does these three very different things. 


I love transactions. If you store data in four systems (three from above plus 
PG), then you have
better performance if you have high load. But what happens if a transaction 
fails (rolls back). Then this
can leave the other data sinks in a broken state. Example: a blob in s3 might 
be updated, but the rollback
in PG does not rollback in s3 

And one other benefit if you have one system: Configuration management is 
easier.

I know that if you have very high load, then you need to optimize.

But in my context the load is far from high. Robust transactions (including
rollback in all related systems) is more important for me.

For the rabbitmq/amqp part I found that the new SKIP LOCKED feature can help:

  https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/

Leaving two things open:

 - blob storage
 - redis/caching

>> One question is "is it possible?", then next "is it feasible?"
> 
> Possible? Sure: p != 0
> Probable? No
> Desirable? No
> 
>> I think it would be great if I could use PG only and if I could
>> avoid the other types of servers.
> 
> When you're holding a hammer, everything looks like a nail. But hammering 
> screws doesn't get you very far. Sometimes you need a screwdriver and on 
> other days a glue gun...

Yes, you are right.


Regards,
  Thomas Güttler

-- 
I am looking for feedback for my personal programming guidelines:
https://github.com/guettli/programming-guidelines


-- 
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] Logical replication

[Adrian Klaver]

On 05/01/2017 09:25 AM, Peter Eisentraut wrote:

On 5/1/17 10:32, Adrian Klaver wrote:

On 04/30/2017 09:07 AM, Adrian Klaver wrote:

I have started looking at the logical replication feature in Postgres
10. One thing I have no been able to determine is the interoperability
between it and pglogical(www.2ndquadrant.com/en/resources/pglogical/). I
know the one is derived from the other, what I can not find is whether a
Postgres 9.4 instance with the pglogical extension installed can
communicate with a Postgres 10 instance using the built in code?



Some testing says the answer is no:


correct


If I am correct, this means from 9.4 <--> 10 and points in between you
would need to use the pglogical extension on both ends.


correct


Going from 10
--> you could use the builtin logical replication. This leads to another
question. Is is possible to use both at the same time?:

9.4   --->   10(instance 1)---> 10(instance 2)
pglogicalpglogical
  builtinbuiltin


That is possible.


Thanks for the information.



pglogical will continue to exist, so you can also keep using it if you
already have it.



I tried building the pglogical extension against Postgres 10 and it 
seems that is not possible yet, which tracks the information on the 
pglogical page:


https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/

I went to the GitHub repo:

https://github.com/2ndQuadrant/pglogical

and did not see anything that looks ready for Postgres 10.

Am I missing something or is this something for the future?




--
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] Logical replication

[Peter Eisentraut]

On 5/1/17 10:32, Adrian Klaver wrote:
> On 04/30/2017 09:07 AM, Adrian Klaver wrote:
>> I have started looking at the logical replication feature in Postgres
>> 10. One thing I have no been able to determine is the interoperability
>> between it and pglogical(www.2ndquadrant.com/en/resources/pglogical/). I
>> know the one is derived from the other, what I can not find is whether a
>> Postgres 9.4 instance with the pglogical extension installed can
>> communicate with a Postgres 10 instance using the built in code?

> Some testing says the answer is no:

correct

> If I am correct, this means from 9.4 <--> 10 and points in between you 
> would need to use the pglogical extension on both ends.

correct

> Going from 10
> --> you could use the builtin logical replication. This leads to another 
> question. Is is possible to use both at the same time?:
> 
> 9.4   --->   10(instance 1)---> 10(instance 2)
> pglogicalpglogical
>   builtinbuiltin

That is possible.

pglogical will continue to exist, so you can also keep using it if you
already have it.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] GENERAL : [Can somebody explain about Base directory]

[Melvin Davidson]

On Mon, May 1, 2017 at 11:41 AM, VENKTESH GUTTEDAR <
venkteshgutte...@gmail.com> wrote:

> Hello All,
>
> Base directory is consuming to much memory, leading to no space on
> server and stopping the application
>
> Somebody please explain why it takes so much memory and is it safe to
> delete those files.?
>
> --
> Regards :
> Venktesh Guttedar.
>
>

*Please clarify. Are your talking about file space or physical memory? They
are two different things.*

*Which version of PostgreSQL are you using?*

*What is your O/S.*


*How many databases have you created in your PostgreSQL server?-- *
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] GENERAL : [Can somebody explain about Base directory]

[Adrian Klaver]

On 05/01/2017 08:41 AM, VENKTESH GUTTEDAR wrote:

Hello All,

Base directory is consuming to much memory, leading to no space on
server and stopping the application


I am going to say that you mean that the base directory is consuming too 
much disk space, correct?


If not can you be specific about what you mean?

Maybe output from a command that shows the issue?



Somebody please explain why it takes so much memory and is it safe
to delete those files.?


Well if you are talking about the base directory in the Postgres 
cluster, them that is where the database data is stored:


https://www.postgresql.org/docs/9.6/static/storage-file-layout.html

"base  Subdirectory containing per-database subdirectories

...

For each database in the cluster there is a subdirectory within 
PGDATA/base, named after the database's OID in pg_database. This 
subdirectory is the default location for the database's files; in 
particular, its system catalogs are stored there.


Each table and index is stored in a separate file. For ordinary 
relations, these files are named after the table or index's filenode 
number, which can be found in pg_class.relfilenode. But for temporary 
relations, the file name is of the form tBBB_FFF, where BBB is the 
backend ID of the backend which created the file, and FFF is the 
filenode number. In either case, in addition to the main file (a/k/a 
main fork), each table and index has a free space map (see Section 
65.3), which stores information about free space available in the 
relation. The free space map is stored in a file named with the filenode 
number plus the suffix _fsm. Tables also have a visibility map, stored 
in a fork with the suffix _vm, to track which pages are known to have no 
dead tuples. The visibility map is described further in Section 65.4. 
Unlogged tables and indexes have a third fork, known as the 
initialization fork, which is stored in a fork with the suffix _init 
(see Section 65.5)."



So if it is growing it is because the databases are growing.



--
Regards :
Venktesh Guttedar.




--
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] GENERAL : [Can somebody explain about Base directory]

[Scott Mead]

On Mon, May 1, 2017 at 11:41 AM, VENKTESH GUTTEDAR <
venkteshgutte...@gmail.com> wrote:

> Hello All,
>
> Base directory is consuming to much memory, leading to no space on
> server and stopping the application
>
> Somebody please explain why it takes so much memory and is it safe to
> delete those files.?
>
>
The base directory is where your actual data (tables, indexes) are stored.
It is NOT safe to delete from that directory manually.  It would be better
to login to the database and see if you can either drop tables or indexes.
If you do a 'DROP TABLE ;' in the database, it will delete from
the base directory.  DO NOT DELETE FROM THE BASE DIRECTORY manually unless
you know what you are doing.

   It is possible that you have bloat causing you space issues, but, that's
a harder thing to solve (
https://www.openscg.com/2016/11/postgresql-bloat-estimates/)


> --
> Regards :
> Venktesh Guttedar.
>
>


-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

[GENERAL] GENERAL : [Can somebody explain about Base directory]

[VENKTESH GUTTEDAR]

Hello All,

Base directory is consuming to much memory, leading to no space on
server and stopping the application

Somebody please explain why it takes so much memory and is it safe to
delete those files.?

-- 
Regards :
Venktesh Guttedar.

Re: [GENERAL] Logical replication

[Adrian Klaver]

On 04/30/2017 09:07 AM, Adrian Klaver wrote:

I have started looking at the logical replication feature in Postgres
10. One thing I have no been able to determine is the interoperability
between it and pglogical(www.2ndquadrant.com/en/resources/pglogical/). I
know the one is derived from the other, what I can not find is whether a
Postgres 9.4 instance with the pglogical extension installed can
communicate with a Postgres 10 instance using the built in code?



Some testing says the answer is no:

postgres=# CREATE SUBSCRIPTION pgsql10sub CONNECTION 
'dbname=replication_test host=localhost user=postgres port=5412' 
PUBLICATION pgsql94;
ERROR:  could not receive list of replicated tables from the publisher: 
ERROR:  syntax error


If I am correct, this means from 9.4 <--> 10 and points in between you 
would need to use the pglogical extension on both ends. Going from 10 
--> you could use the builtin logical replication. This leads to another 
question. Is is possible to use both at the same time?:


9.4   --->   10(instance 1)---> 10(instance 2)
pglogicalpglogical
 builtinbuiltin

--
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] all serial type was changed to 1

[Adrian Klaver]

On 04/30/2017 10:51 PM, Max Wang wrote:

Hi All,



We have a PostgreSQL database. There are 26 tables and we use serial
type as primary key.  We had a insert error as “duplicate key value
violates unique constraint, DETAIL:  Key (id)=(1) already exists.” one
weeks ago. I checked and found all tables’ id were reset to 1.


So to be clear:

Every row in each of the 26 tables has an id of 1?

or

Do you mean the sequences where reset to 1?





I checked database log and did not find any useful information.  I am
not sure why this happen. The only script which connect to this database
is a Python script and only do normal insert/update/delete actions.



Please give me some suggestions if you happen to know something about
this issue. I appreciate any feedback you might have.



I am very new to PostgreSQL and this mail list. Please let me know if I
did not something wrong.



Thank you.



Regards,

Max




--
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] all serial type was changed to 1

[David Rowley]

On 1 May 2017 at 17:51, Max Wang  wrote:
> We have a PostgreSQL database. There are 26 tables and we use serial type as
> primary key.  We had a insert error as “duplicate key value violates unique
> constraint, DETAIL:  Key (id)=(1) already exists.” one weeks ago. I checked
> and found all tables’ id were reset to 1.

Sounds like something that might happen if you'd just bulk loaded the
data and didn't set the sequences.

If you really did use serial types then you could set all these to the
max value of the column which they belong to.

The following will give you a list of commands to execute:

SELECT 'select setval(''' || c.relname || ''', max(' ||
quote_ident(a.attname) || ')) from ' || d.refobjid::regclass || ';'
FROM pg_depend d
INNER JOIN pg_class c ON d.objid = c.oid
INNER JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum =
d.refobjsubid
WHERE c.relkind = 'S' AND d.refclassid = 1259;

You may like to check that returns 26 rows as you expect and verify
that all those sequences do need reset before running the command.

If you're running Postgres 9.6 and using psql, you can execute the
above then execute \gexec which will execute the previous result set
as commands.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] all serial type was changed to 1

[Amitabh Kant]

On Mon, May 1, 2017 at 11:21 AM, Max Wang  wrote:

> Hi All,
>
>
>
> We have a PostgreSQL database. There are 26 tables and we use serial type
> as primary key.  We had a insert error as “duplicate key value violates
> unique constraint, DETAIL:  Key (id)=(1) already exists.” one weeks ago. I
> checked and found all tables’ id were reset to 1.
>
>
>
> I checked database log and did not find any useful information.  I am not
> sure why this happen. The only script which connect to this database is a
> Python script and only do normal insert/update/delete actions.
>
>
>
> Please give me some suggestions if you happen to know something about this
> issue. I appreciate any feedback you might have.
>
>
>
> I am very new to PostgreSQL and this mail list. Please let me know if I
> did not something wrong.
>
>
>
> Thank you.
>
>
>
> Regards,
>
> Max
>


By any chance, has it to do anything with the Cycle option of sequences:

https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

Re: [GENERAL] all serial type was changed to 1

[Peter Geoghegan]

On Sun, Apr 30, 2017 at 10:51 PM, Max Wang  wrote:
> We have a PostgreSQL database. There are 26 tables and we use serial type as
> primary key.  We had a insert error as “duplicate key value violates unique
> constraint, DETAIL:  Key (id)=(1) already exists.” one weeks ago. I checked
> and found all tables’ id were reset to 1.

I've heard of this happening before. I never determined what the cause was.


-- 
Peter Geoghegan

VMware vCenter Server
https://www.vmware.com/


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