Re: [GENERAL] Re: any way for a transaction to "see" inserts done earlier in the transaction?

On 17 Apr 2014, at 2:49, David G Johnston wrote: > Robert DiFalco wrote >> Two common cases I can think of: >> >> 1. The PERL framework is only caching the insert and does not actually >> perform it until commit is issued. > > Wouldn't the same mechanism cache the corresponding SELECT? Not lik

Re: [GENERAL] shared memory allocation - C function

On Thu, Apr 17, 2014 at 8:04 AM, Alan Nilsson wrote: > Is it possible to allocate a small chunk of shared memory outside of any > pools(i.e. manually alloced & de-alloced) that is visible to all processes? > I would like a small amount to store a mutex and a condition variable that > is accessib

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

On 04/16/2014 05:24 PM, Susan Cassidy wrote: I marked it volatile, and still the next time I call the function after the first insert, using the previous new id as as input parameter, it still can't "find" the newly inserted id for the next go-round. Nor can any regular SELECTs in the main progr

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

On 04/16/2014 07:06 PM, Susan Cassidy wrote: Yes, it is the same connection. It is all the same transaction. Susan On Wed, Apr 16, 2014 at 5:00 PM, John R Pierce mailto:pie...@hogranch.com>> wrote: On 4/16/2014 4:53 PM, Susan Cassidy wrote: Well, it isn't working for me right no

[GENERAL] Re: any way for a transaction to "see" inserts done earlier in the transaction?

I'm presuming the OP is using the typical model of: conn = getConnection() id = doInsert(conn) rst = doSelect(conn, id) doSomething(rst) conn.commit() conn.relrease() Robert DiFalco wrote > Two common cases I can think of: > > 1. The PERL framework is only caching the insert and does not actuall

[GENERAL] Re: any way for a transaction to "see" inserts done earlier in the transaction?

Susan Cassidy-3 wrote > Nor can any regular SELECTs in the main program find it. Ever? If this is a same transaction visibility issue then when your Perl program stops you should be able to go find that ID manually to confirm it was inserted and committed properly. If you still cannot find the I

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

On 04/16/2014 05:24 PM, Susan Cassidy wrote: I marked it volatile, and still the next time I call the function after the first insert, using the previous new id as as input parameter, it still can't "find" the newly inserted id for the next go-round. Nor can any regular SELECTs in the main progr

Re: [GENERAL] Re: any way for a transaction to "see" inserts done earlier in the transaction?

Two common cases I can think of: 1. The PERL framework is only caching the insert and does not actually perform it until commit is issued. 2. You really are not on the same transaction even though it appears you are and the transaction isolation is such that you cannot see the insert until it is f

[GENERAL] Re: any way for a transaction to "see" inserts done earlier in the transaction?

One possibility is that the INSERT is going to a different table (having the same name but existing in a different schema) that is visible/default to the function but not outside of it. Or the function on the server is not "current" and thus isn't doing what you think it is. > I do an insert v

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

I marked it volatile, and still the next time I call the function after the first insert, using the previous new id as as input parameter, it still can't "find" the newly inserted id for the next go-round. Nor can any regular SELECTs in the main program find it. Susan On Wed, Apr 16, 2014 at 5:

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

It isn't marked as one of those as all, so whatever the default is. That could be it. I'll look up the default. Thanks, Susan On Wed, Apr 16, 2014 at 5:17 PM, Tom Lane wrote: > Susan Cassidy writes: > > It is a fairly large and complex Perl program, so no, not really. > > I do an insert via

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

Susan Cassidy writes: > It is a fairly large and complex Perl program, so no, not really. > I do an insert via a function, which returns the new id, then later I try > to SELECT on that id, and it doesn't find it. > Could it be because the insert is done inside a function? Is the SELECT also ins

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

It seems to be returning the right id. It should be next for the serial datatype. Susan On Wed, Apr 16, 2014 at 5:13 PM, Bosco Rama wrote: > On 04/16/14 17:08, Susan Cassidy wrote: > > The function does a select to see if the id number exists, and it fails. > > NOT FOUND causes a RAISE EXCEPT

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

Not to say that perl and complex are redundant, but does the id go away after the NOT FOUND exception? On 04/16/2014 06:08 PM, Susan Cassidy wrote: The function does a select to see if the id number exists, and it fails. NOT FOUND causes a RAISE EXCEPTION. Susan On Wed, Apr 16, 2014 at 5:

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

On 04/16/14 17:08, Susan Cassidy wrote: > The function does a select to see if the id number exists, and it fails. > NOT FOUND causes a RAISE EXCEPTION. Is it returning the right id? I seem to remember a recent thread about Perl DBI returning the wrong id's for certain operations. Just at though

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

The function does a select to see if the id number exists, and it fails. NOT FOUND causes a RAISE EXCEPTION. Susan On Wed, Apr 16, 2014 at 5:05 PM, Susan Cassidy < susan.cass...@decisionsciencescorp.com> wrote: > It is a fairly large and complex Perl program, so no, not really. > > I do an inse

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

It is a fairly large and complex Perl program, so no, not really. I do an insert via a function, which returns the new id, then later I try to SELECT on that id, and it doesn't find it. Could it be because the insert is done inside a function? Susan On Wed, Apr 16, 2014 at 4:58 PM, Steven Schl

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

Yes, it is the same connection. It is all the same transaction. Susan On Wed, Apr 16, 2014 at 5:00 PM, John R Pierce wrote: > On 4/16/2014 4:53 PM, Susan Cassidy wrote: > >> Well, it isn't working for me right now. It can't "see" a row that was >> inserted earlier in the transaction. It is

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

On 4/16/2014 4:53 PM, Susan Cassidy wrote: Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found. are you using the same connection ? it won't be visible to a different con

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

>> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane wrote: >> Susan Cassidy writes: >> > Is there any way to let a transaction "see" the inserts that were done >> > earlier in the transaction? >> >> It works that way automatically, as long as you're talking about separate >> statements within one tra

Re: [GENERAL] client encoding that psql command sets

On Fri, Feb 7, 2014 at 09:12:10AM +, Albe Laurenz wrote: > > Even when a LC_CTYPE environment variable was set up, the result did not > > change. > > What do you think? > > I think that the documentation contradicts the code. > > In bin/psql/settings.h: > > typedef struct _psqlSettings > {

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found. Susan On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane wrote: > Susan Cassidy writes: > > Is there any way to let a transaction

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

Susan Cassidy writes: > Is there any way to let a transaction "see" the inserts that were done > earlier in the transaction? It works that way automatically, as long as you're talking about separate statements within one transaction. regards, tom lane -- Sent via pgsql

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

On Apr 16, 2014, at 4:27 PM, Susan Cassidy wrote: > Is there any way to let a transaction "see" the inserts that were done > earlier in the transaction? I want to insert a row, then later use it within > the same transaction. > > If not, I will have to commit after each insert, and I don't

[GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

Is there any way to let a transaction "see" the inserts that were done earlier in the transaction? I want to insert a row, then later use it within the same transaction. If not, I will have to commit after each insert, and I don't want to do that until add the rows are added, if I can possibly av

[GENERAL] shared memory allocation - C function

Is it possible to allocate a small chunk of shared memory outside of any pools(i.e. manually alloced & de-alloced) that is visible to all processes? I would like a small amount to store a mutex and a condition variable that is accessible in a c function from any process. Is there an SPI/API to

Re: [GENERAL] hot standby data folder bigger than primary

On 4/16/2014 2:52 PM, yhe wrote: hi, I have a primary/standby setup and I noticed after many days running, the standby's data folder is growing bigger than the primary, and it keeps growing bigger, now it is almost 4 times the size, eg, primary data folder is 2G, standby is 8G. Does anyone know

Re: [GENERAL] Heartbleed Impact

On Thu, Apr 17, 2014 at 1:31 AM, John R Pierce wrote: > do you enable SSL and expose it to an insecure network ? if not, no > exposure to the heartbleed bug. > No, SSL is not enabled in my case but also wanted to make sure there is no binary available which can later result into any potential

Re: [GENERAL] Heartbleed Impact

On 4/16/2014 12:40 PM, Dev Kumkar wrote: So does this mean PostgreSQL binaries available on EnterpriseDB has an impact for windows ? Can you help me with the binary name? do you enable SSL and expose it to an insecure network ? if not, no exposure to the heartbleed bug. AFAIK, the bin

[GENERAL] hot standby data folder bigger than primary

hi, I have a primary/standby setup and I noticed after many days running, the standby's data folder is growing bigger than the primary, and it keeps growing bigger, now it is almost 4 times the size, eg, primary data folder is 2G, standby is 8G. Does anyone know why it is so? best, Ying --

Re: [GENERAL] Heartbleed Impact

On Thu, Apr 17, 2014 at 12:53 AM, John R Pierce wrote: > windows native stuff uses completely different TLS libraries, SChannel and > stuff. AFAIK, these aren't subject to this bug, which was specific to > OpenSSL 1.0.1x for x=a-f...openssl is only used on windows when someone > uses it expl

Re: [GENERAL] Heartbleed Impact

On 4/16/2014 9:38 AM, Dev Kumkar wrote: What is the windows equivalent of libssl.so.1.0.0 ? Please reply as this is really becoming priority for me. windows native stuff uses completely different TLS libraries, SChannel and stuff. AFAIK, these aren't subject to this bug, which was specific

Re: [GENERAL] Approach to Data Summary and Analysis

Thanks Roxanne, I suppose when it comes down to it -- for the current use cases and data size -- my only concern is the "calling" query that will need to use max to determine if a user has already had a call today. For a large user set, for each user I would either have to MAX on the answered times

Re: [GENERAL] Heartbleed Impact

Hey, What is the windows equivalent of libssl.so.1.0.0 ? Please reply as this is really becoming priority for me. Regards...

Re: [GENERAL] timezone datetime issue

On 04/16/2014 03:56 AM, Glenn Pierce wrote: Hi I have an issue with adjusting a timestamp. I have a table like CREATE TABLE sensor_values ( ts timestamp with time zone NOT NULL, value double precision NOT NULL DEFAULT 'NaN'::real, ) It was intended that ts timestamps would be the

Re: [GENERAL] Heartbleed Impact

On Wed, Apr 16, 2014 at 6:49 PM, Albe Laurenz wrote: > Dev Kumkar wrote: > >> Unless somebody changes the setting to ssl=on, there should be no > problem. > > > Thanks also please help to understand - does changing this > postgresql.conf setting enough to be > > vulnerable here? > > Just changing

Re: [GENERAL] Approach to Data Summary and Analysis

On 4/15/2014 9:10 PM, Robert DiFalco wrote: 1. >500K rows per day into the calls table. 2. Very rarely. The only common query is gathering users that have not been called "today" (along with some other qualifying criteria). More analytical queries/reports are done for internal use and it is not

Re: [GENERAL] Heartbleed Impact

On Wed, Apr 16, 2014 at 7:50 PM, Stephen Frost wrote: > * Dev Kumkar (devdas.kum...@gmail.com) wrote: > > I just downloaded the latest binaries from EnterpriseDB and when checked > > with libssl.so.1.0.0 can see this: > > OpenSSL 1.0.1g 7 Apr 2014 > > > > OpenSSL 1.0.1g is the patched version. >

Re: [GENERAL] Grants where grantor = grantee?

"Colin 't Hart" writes: > Looking through our database I find some cases of grants where grantor > = grantee. > Is this ever a useful thing to do? I can't imagine why. Revoke them and you'll find out ;-) I suppose you are referring to the object owner's own default privileges, which are treated

Re: [GENERAL] Heartbleed Impact

* Dev Kumkar (devdas.kum...@gmail.com) wrote: > I just downloaded the latest binaries from EnterpriseDB and when checked > with libssl.so.1.0.0 can see this: > OpenSSL 1.0.1g 7 Apr 2014 > > OpenSSL 1.0.1g is the patched version. Yes, checked w/ them and they say it's all patched.. > Awaiting con

Re: [GENERAL] json_populate_recordset and nested object, how to solve?

MatheusOl helped me solve this on IRC, sending it here in case it helps someone looking at the archives of the mailing list. Here is a test case create table t(id SERIAL, event json); insert into t(event) VALUES ('{"type":"show", "products": [ { "id" : 1, "name" : "p1"}] }'::json); insert into t(

Re: [GENERAL] timezone datetime issue

On 04/16/2014 04:19 AM, Glenn Pierce wrote: Although I guess something like this would do it ? UPDATE sensor_values ts = ts - interval (1 hour) WHERE ts BETWEEN ('2014-03-30 01:00', '2014-10-26 02:00') I would say: UPDATE sensor_values ts = ts - interval '1 hour' WHERE ts BETWEEN ('2014-03-

Re: [GENERAL] Heartbleed Impact

On Wed, Apr 16, 2014 at 6:54 PM, Stephen Frost wrote: > > Yeah, I'm doing that already and they're looking into it right now. > > Thanks, > > Stephen > I just downloaded the latest binaries from EnterpriseDB and when checked with libssl.so.1.0.0 can see this: OpenSSL 1.0.

Re: [GENERAL] Heartbleed Impact

* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > Dev Kumkar wrote: > > > of which OpenSSL package versions' libssl.1.0.0.so is available at > > > http://www.enterprisedb.com/products-services-training/pgbindownload ? > > > > > > > Ok, looked at the STRINGS versions and the "OpenSSL 1.0.1f 6 Ja

Re: [GENERAL] Heartbleed Impact

Dev Kumkar wrote: >> Unless somebody changes the setting to ssl=on, there should be no problem. > Thanks also please help to understand - does changing this postgresql.conf > setting enough to be > vulnerable here? Just changing the setting will only cause your database server to error out on re

Re: [GENERAL] Heartbleed Impact

Dev Kumkar wrote: > > of which OpenSSL package versions' libssl.1.0.0.so is available at > > http://www.enterprisedb.com/products-services-training/pgbindownload ? > > > > Ok, looked at the STRINGS versions and the "OpenSSL 1.0.1f 6 Jan 2014" is > seen. > > Please let me know if the new binary i

Re: [GENERAL] Querying all documents for a company and its projects etc

what about resolving the join "document doc JOIN document_usage du ON doc.id = du.document_id" using WITH? I think this won't work because the result of this CTE would be huge, but you could get the company with that id first using a WITH clause too, then you would have the name and you wouldn't ha

Re: [GENERAL] replace function, what happens afterwards?

On 16/04/2014 13:54, MirrorX wrote: > dear all, > > i have a question regarding a recent problem that we faced and we are > trying to identify. let's suppose we have a function A and a function > B that in some point calls function A. > > function A-> ….. insert into table1(col1,col2) values ($1,

[GENERAL] replace function, what happens afterwards?

dear all, i have a question regarding a recent problem that we faced and we are trying to identify. let's suppose we have a function A and a function B that in some point calls function A. function A-> ….. insert into table1(col1,col2) values ($1,$2) ….. function B -> … select… update…. insert…

Re: [GENERAL] Querying all documents for a company and its projects etc

På mandag 14. april 2014 kl. 21:55:26, skrev David G Johnston < david.g.johns...@gmail.com >: A couple of thoughts: 1) The "union" really only pertains to the entity table rows; once you "union all" those (duplicates should not matter and probably will not eve

Re: [GENERAL] Heartbleed Impact

On Wed, Apr 16, 2014 at 5:28 PM, Dev Kumkar wrote: > On Wed, Apr 16, 2014 at 4:57 PM, Boszormenyi Zoltan wrote: > >> The package version and the soversion are only loosely related. >> E.g .the upstream OpenSSL 1.0.0 and 1.0.1 series both ship soversion >> 1.0.0. >> >> Best regards, >> Zoltán Bös

[GENERAL] Grants where grantor = grantee?

Hi, Looking through our database I find some cases of grants where grantor = grantee. Is this ever a useful thing to do? I can't imagine why. Thanks, Colin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/ma

Re: [GENERAL] Heartbleed Impact

On Wed, Apr 16, 2014 at 3:18 PM, Albe Laurenz wrote: > > Unless somebody changes the setting to ssl=on, there should be no problem. > > Yours, > Laurenz Albe > Thanks also please help to understand - does changing this postgresql.conf setting enough to be vulnerable here? Regards...

Re: [GENERAL] Heartbleed Impact

On Wed, Apr 16, 2014 at 4:57 PM, Boszormenyi Zoltan wrote: > The package version and the soversion are only loosely related. > E.g .the upstream OpenSSL 1.0.0 and 1.0.1 series both ship soversion 1.0.0. > > Best regards, > Zoltán Böszörményi of which OpenSSL package versions' libssl.1.0.0.so is

Re: [GENERAL] Heartbleed Impact

On 16 April 2014 21:27, Boszormenyi Zoltan wrote: > 2014-04-16 12:40 keltezéssel, Tony Theodore írta: >> 1.0.0 isn't affected. > > > The package version and the soversion are only loosely related. > E.g .the upstream OpenSSL 1.0.0 and 1.0.1 series both ship soversion 1.0.0. Good point - thanks!

Re: [GENERAL] timezone datetime issue

Glenn Pierce wrote: > I have a table like > > CREATE TABLE sensor_values > ( > ts timestamp with time zone NOT NULL, > value double precision NOT NULL DEFAULT 'NaN'::real, > ) > > It was intended that ts timestamps would be the time we wanted to store in > UTC. > Clients would adjus

Re: [GENERAL] Heartbleed Impact

2014-04-16 12:40 keltezéssel, Tony Theodore írta: On 16 April 2014 18:48, Dev Kumkar wrote: We embed certain binaries and libssl.so.1.0.0 gets shipped along with pre-build in-house database with product. 1.0.0 isn't affected. The package version and the soversion are only loosely related. E

Re: [GENERAL] timezone datetime issue

Although I guess something like this would do it ? UPDATE sensor_values ts = ts - interval (1 hour) WHERE ts BETWEEN ('2014-03-30 01:00', '2014-10-26 02:00') On 16 April 2014 11:56, Glenn Pierce wrote: > Hi I have an issue with adjusting a timestamp. > > I have a table like > > CREATE TABLE s

[GENERAL] timezone datetime issue

Hi I have an issue with adjusting a timestamp. I have a table like CREATE TABLE sensor_values ( ts timestamp with time zone NOT NULL, value double precision NOT NULL DEFAULT 'NaN'::real, ) It was intended that ts timestamps would be the time we wanted to store in UTC. Clients would

Re: [GENERAL] Heartbleed Impact

On 16 April 2014 18:48, Dev Kumkar wrote: > We embed certain binaries and libssl.so.1.0.0 gets shipped along with > pre-build in-house database with product. 1.0.0 isn't affected. Cheers, Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subs

Re: [GENERAL] Heartbleed Impact

Dev Kumkar wrote: > Can you please let us know about the impact in case binaries are being > shipped and SSL is off? Unless somebody changes the setting to ssl=on, there should be no problem. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

Re: [GENERAL] [GENARAL] round() bug

Raymond O'Donnell wrote: >>> Interestingly, I get different results (on both 9.1.4 and 9.3.0) on >>> Windows: >> I'm not particularly surprised that Windows is not being IEEE compliant, and >> instead chooses the more common round-away-from-zero behavior, here though I >> am unsure where the depen

[GENERAL] Heartbleed Impact

We are using postgresql binaries downloaded from here http://www.enterprisedb.com/products-services-training/pgbindownload The binaries which are currently at 9.3.3 were updated when the security vulnerabilities were announced in Feb 2014. We embed certain binaries and libssl.so.1.0.0 gets shippe