Re: [GENERAL] Unique index problem

Thank you. I used the syntax with 2 indexes, it works for me. But why does NULL != NULL? -- Original Message -- From: "Marc Mamin" To: "Sterpu Victor" Cc: "PostgreSQL General" ; "Andreas Kretschmer"

Re: [GENERAL] Re: Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2

On 12/20/15 3:43 PM, Amitabh Kant wrote: Just did a transfer without a hitch on a 1 TB (approx) production database . Everything is working fine. Saved a ton of time and complications. Note that that would not work if there were any binary incompatibilities between the two systems. Something

[GENERAL] Threads in PostgreSQL

Hi, Is it possible to use threads in Postgresql ?? I am using threads in my foreign data wrapper and i get the following error when i use the threads . *ERROR: stack depth limit exceeded* *HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the

Re: [GENERAL] Unique index problem

2015-12-21 7:39 GMT+01:00 Sterpu Victor : > Thank you. > > I used the syntax with 2 indexes, it works for me. > But why does NULL != NULL? > > because it was designed http://www.w3schools.com/sql/sql_null_values.asp Pavel > > -- Original Message -- > From: "Marc

Re: [GENERAL] Does PostgreSQL support to write glusterfs by Libgfapi

Hi, Thank you. This was designed storage solution deployed in cloud among with other storage usage. We will remove glusterfs solution to DB usage. We don't intend to use glusterfs to do data replication. But PostgreSQL may think about variable storage solutions. -- View this message in

Re: [GENERAL] Test disk reliability (or HGST HTS721010A9E630 surprisingly reliable)

On 12/20/15 1:09 PM, Félix GERZAGUET wrote: After reading http://www.postgresql.org/docs/current/static/wal-reliability.html, I tried the recommended diskchecker.pl but I am not satisfied: I always get: Total errors: 0 even if I tested with with a

Re: [GENERAL] [HACKERS] Threads in PostgreSQL

On Mon, Dec 21, 2015 at 11:51 AM, sri harsha wrote: > Hi, > >Is it possible to use threads in Postgresql ?? I am using threads in my > foreign data wrapper and i get the following error when i use the threads . > > *ERROR: stack depth limit exceeded* > *HINT:

Re: [GENERAL] Threads in PostgreSQL

On 12/20/2015 10:21 PM, sri harsha wrote: Is it possible to use threads in Postgresql ?? multi-threading is not used anywhere in postgresql, and is not at all a good idea. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list

Re: [GENERAL] Unique index problem

On 12/20/15 10:18 AM, Andreas Kretschmer wrote: test=*# create unique index on foo(a,b,c) where a is not null and b is not null and c is not null; CREATE INDEX As you discovered, you'd have to build separate indexes for each of the nullable fields: UNIQUE ON (a,b) WHERE c IS NULL a,c WHERE

Re: [GENERAL] Does PostgreSQL support to write glusterfs by Libgfapi

Hi, Thank you for the advice.This was designed storage solution deployed in cloud among with other storage usage. We will remove glusterfs solution to DB usage. -- View this message in context:

[GENERAL] Re: Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2

On Sun, Dec 20, 2015 at 6:22 PM, Amitabh Kant wrote: > Thanks Andreas .. I thought of going the replication way .. But having > done a sample run couple of hours ago, it seems it will work out without > making any changes . Will update this thread once I have done the

Re: [GENERAL] Unique index problem

pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] im Auftrag von Scott Marlowe [scott.marl...@gmail.com] ndet: Sonntag, 20. Dezember 2015 17:02 Sterpu Victor PostgreSQL General eff: Re: [GENERAL] Unique index problem un, Dec 20, 2015

[GENERAL] Re: Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2

Thanks Andreas .. I thought of going the replication way .. But having done a sample run couple of hours ago, it seems it will work out without making any changes . Will update this thread once I have done the final run . Amitabh On Sunday 20 December 2015, Andreas Kretschmer

Re: [GENERAL] Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2

Read the doc about Replikation. I think you can simply set up the New machine AS streaming Replikation slave and promote it than AS master - with no downtime. Am 20. Dezember 2015 02:50:57 MEZ, schrieb Amitabh Kant : >Hi > >I have a Postgresql 9.1 instance running on

Re: [GENERAL] Session Identifiers

2015-12-20 21:00 GMT+03:00 Pavel Stehule : > > > 2015-12-20 18:56 GMT+01:00 Dmitry Igrishin : > >> >> >> 2015-12-20 19:44 GMT+03:00 Pavel Stehule : >> >>> >>> >>> 2015-12-20 17:30 GMT+01:00 Dmitry Igrishin :

Re: [GENERAL] Session Identifiers

2015-12-20 19:08 GMT+01:00 Dmitry Igrishin : > > > 2015-12-20 21:00 GMT+03:00 Pavel Stehule : > >> >> >> 2015-12-20 18:56 GMT+01:00 Dmitry Igrishin : >> >>> >>> >>> 2015-12-20 19:44 GMT+03:00 Pavel Stehule :

[GENERAL] Test disk reliability (or HGST HTS721010A9E630 surprisingly reliable)

Hello, I am trying to assess disk reliability. After reading http://www.postgresql.org/docs/current/static/wal-reliability.html, I tried the recommended diskchecker.pl but I am not satisfied: I always get: Total errors: 0 even if I tested with with a

Re: [GENERAL] Threads in PostgreSQL

On 12/21/15 01:24, Atri Sharma wrote: > On Mon, Dec 21, 2015, sri harsha wrote: > >> I am using threads in my >> foreign data wrapper and i get the following error when i use the threads . >> >> *ERROR: stack depth limit exceeded* >> *HINT: Increase the configuration

Re: [GENERAL] [HACKERS] Threads in PostgreSQL

Hi, PostgreSQL is not using threads but it is possible to spawn thread in your PostgreSQL extensions. For example, I have used pool of threads in my IMCS extension. But you need to build your extension with -pthread: CUSTOM_COPT = -pthread Also, please take in account that many PostgreSQL

[GENERAL] dblink connect per socket / specify cluster name

Dear list, i'm running PgSQL 9.3 on Debian installed from the Debian packets. I'm using dblink to access a different database. When im trying to connect to the database by socket via dblink_exec('dbname=test host=/var/run/postgresql' .. everything works nicely if there's just one db-cluster named

[GENERAL] Session Identifiers

Greetings! I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my questions: >From the security standpoint we have to assure that database invalidates session identifiers upon user logout or other session termination (timeout counts

Re: [GENERAL] Unique index problem

On Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor wrote: > Hello > > I created a unique index that doesn't seem to work when one column is NULL. > Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON > lab_tests_groups_siui(id_lab_tests_siui,

Re: [GENERAL] Unique index problem

On Sun, Dec 20, 2015 at 9:00 AM, Scott Marlowe wrote: > On Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor wrote: >> Hello >> >> I created a unique index that doesn't seem to work when one column is NULL. >> Index is created like this: CREATE UNIQUE INDEX

Re: [GENERAL] Unique index problem

Sterpu Victor wrote: > Hello > > I created a unique index that doesn't seem to work when one column is NULL. > Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON > lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, >

Re: [GENERAL] Session Identifiers

Hi 2015-12-20 16:16 GMT+01:00 oleg yusim : > Greetings! > > I'm new to PostgreSQL, working on it from the point of view of Cyber > Security assessment. In regards to the here is my questions: > > From the security standpoint we have to assure that database invalidates >

Re: [GENERAL] Unique index problem

Andreas Kretschmer wrote: > > Maybe there are better solutions, it's a quick hack ;-) better solution: test=*# create unique index on foo(a,b,c) where a is not null and b is not null and c is not null; CREATE INDEX (partial index) Andreas -- Really, I'm not out

Re: [GENERAL] Session Identifiers

Hi Melvin, Thank you very much, that logging option really helps (I need to give instructions, people, who are not very code literate should be capable of executing). And, point taken about exact version and enviornment - PostgreSQL 9.4.5, Linux box. Thanks, Oleg On Sun, Dec 20, 2015 at 10:19

Re: [GENERAL] Session Identifiers

Got it, thanks... Now, is it any protection in place currently against replacing Session ID (my understanding, it is kept in memory, belonging to the session process) or against guessing Session ID (i.e. is Session ID generated using FIPS 140-2 compliant algorithms, or anything of that sort)?

Re: [GENERAL] Session Identifiers

Regarding timeouts, PostgreSQL will use the system tcp_keepalives_* parms by default, but you can also configure it separately in postgresql.conf. http://www.postgresql.org/docs/9.4/static/runtime-config-connection.html I suggest you review all available parameters in the postgresql.conf, as it

Re: [GENERAL] Session Identifiers

Thanks you very much Melvin, once again, very useful. So, let me see if I got it right, following configuration should cause my database connection to terminate in 15 minutes, right? tcp_keepalives_idle = 900 tcp_keepalives_interval=1 tcp_keepalives_count=3 Oleg On Sun, Dec 20, 2015 at 11:14

Re: [GENERAL] Session Identifiers

oleg yusim writes: > Got it, thanks... Now, is it any protection in place currently against > replacing Session ID (my understanding, it is kept in memory, belonging to > the session process) or against guessing Session ID (i.e. is Session ID > generated using FIPS 140-2

Re: [GENERAL] Session Identifiers

Actually, I'm not an expert on the tcp_keepalives, but I believe the tcp_keepalives_count should be 1, otherwise it will take 45 minutes minutes to timeout. Then again, I could be wrong. On Sun, Dec 20, 2015 at 12:28 PM, Tom Lane wrote: > oleg yusim

Re: [GENERAL] Session Identifiers

Tom, I understand the idea that for external communication you rely on SSL. However, how about me opening psql prompt into the database directly from my Linux box, my db is installed at? I thought, it would be considered local connection and would not go through the SSL channels. If that is the

Re: [GENERAL] Session Identifiers

Thanks Melvin, Let me experiment with it for a bit. I will let you know results. Oleg On Sun, Dec 20, 2015 at 11:33 AM, Melvin Davidson wrote: > Actually, I'm not an expert on the tcp_keepalives, but I believe the > tcp_keepalives_count > should be 1, otherwise it will

Re: [GENERAL] Session Identifiers

2015-12-20 18:37 GMT+01:00 oleg yusim : > Tom, > > I understand the idea that for external communication you rely on SSL. > However, how about me opening psql prompt into the database directly from > my Linux box, my db is installed at? I thought, it would be considered >

Re: [GENERAL] Session Identifiers

So Pavel, are are saying there is no such thing as Session ID in PostgreSQL DB at all? Everything is tight to the process, session is accociated with, so in essence pid is session id? Oleg On Sun, Dec 20, 2015 at 11:40 AM, Pavel Stehule wrote: > > > 2015-12-20 18:37

Re: [GENERAL] Session Identifiers

2015-12-20 18:45 GMT+01:00 oleg yusim : > So Pavel, are are saying there is no such thing as Session ID in > PostgreSQL DB at all? Everything is tight to the process, session is > accociated with, so in essence pid is session id? > There is backendId and processid, but these

Re: [GENERAL] Session Identifiers

2015-12-20 19:44 GMT+03:00 Pavel Stehule : > > > 2015-12-20 17:30 GMT+01:00 Dmitry Igrishin : > >> Can be totally different if you use some connection pooler like pgpool or >>> pgbouncer - these applications can reuse Postgres server sessions for more

Re: [GENERAL] Session Identifiers

2015-12-20 18:56 GMT+01:00 Dmitry Igrishin : > > > 2015-12-20 19:44 GMT+03:00 Pavel Stehule : > >> >> >> 2015-12-20 17:30 GMT+01:00 Dmitry Igrishin : >> >>> Can be totally different if you use some connection pooler like pgpool

[GENERAL] Unique index problem

Hello I created a unique index that doesn't seem to work when one column is NULL. Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types); Now I can run this insert twice and I

Re: [GENERAL] Session Identifiers

PostgreSQL does not "store" the session_id per se in any system catalogs/tables, however, you can configure the log_line_prefix in postgresql.conf to record it for each connection. It will then be stored in the postgresql log file. Please not that in the future, it is always helpful to provide the

Re: [GENERAL] Session Identifiers

On 12/20/2015 09:16 AM, oleg yusim wrote: Greetings! I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my questions: From the security standpoint we have to assure that database invalidates session identifiers upon user

Re: [GENERAL] Unique index problem

Andreas Kretschmer wrote: > Andreas Kretschmer wrote: > > > > > Maybe there are better solutions, it's a quick hack ;-) > > better solution: sorry, doesn't work =:( Andreas -- Really, I'm not out to destroy Microsoft. That will just

Re: [GENERAL] Session Identifiers

> > Can be totally different if you use some connection pooler like pgpool or > pgbouncer - these applications can reuse Postgres server sessions for more > user sessions. > BTW, AFAIK, it's not possible to change the session authentication information by using SET SESSION AUTHORIZATION [1] if the

Re: [GENERAL] Session Identifiers

2015-12-20 17:30 GMT+01:00 Dmitry Igrishin : > Can be totally different if you use some connection pooler like pgpool or >> pgbouncer - these applications can reuse Postgres server sessions for more >> user sessions. >> > BTW, AFAIK, it's not possible to change the session

Re: [GENERAL] Session Identifiers

Hi Pavel, Thanks, for your response, it helps. Now, from my observations (PostgreSQL 9.4.5, installed on Linux box), if I enter psql prompt at my ssh to the box session and leave it open like that, it doesn't time out. Is it really a case? Session to PostgreSQL DB doesn't terminate on timeout (or

Re: [GENERAL] Session Identifiers

2015-12-20 17:52 GMT+01:00 oleg yusim : > Hi Pavel, > > Thanks, for your response, it helps. Now, from my observations (PostgreSQL > 9.4.5, installed on Linux box), if I enter psql prompt at my ssh to the box > session and leave it open like that, it doesn't time out. Is it

Re: [GENERAL] Session Identifiers

On Sun, Dec 20, 2015 at 11:25:45AM -0600, oleg yusim wrote: > Thanks you very much Melvin, once again, very useful. So, let me see if I > got it right, following configuration should cause my database connection > to terminate in 15 minutes, right? > > tcp_keepalives_idle = 900 >

Re: [GENERAL] Session Identifiers

Hi Andrew, Exactly! Vulnerability is the direct analogy of one with Unix shell. The way we generally deal with Unix shell vulnerability, we configure the shell to terminate on its own if timeout was exceeded. The question here is, can we configure psql client to behave the same? Thanks, Oleg