Re: [GENERAL] Heartbleed Impact

[Stephen Frost]

* Dev Kumkar (devdas.kum...@gmail.com) wrote:
> > AFAIK, the binary name is postgres.exe, from what I've read they are
> > static linking openssl.  the updated versions on the site linked in another
> > message are fixed per the note on that page.
> > http://www.enterprisedb.com/products-services-training/pgdownload
> 
> http://www.enterprisedb.com/products-services-training/pgbindownload also
> has the note added sometime back.
> I was able to verify for Linux binaries looking at STRINGS of so file but
> was not sure about the windows side and hence was looking for confirmation.

All the binaries on both pages have been updated (and were a while
back).  They recently added the 'Note' to the binary downloads page to
clarify this (it was just the Note that had been missing- the binaries
themselves have been updated for a while).

Thanks,

Stephen


signature.asc
Description: Digital signature

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

[yhe]

hi, Andy,

We are using streaming hot standby, no wal shipping. 

I checked and base and pg_xlog. both are fine. 

The extra space is used by /pg_multixact/members(about 5G) and
/pg_multixact/offsets(about 1.5G)

The doc said that pg_multixact contains multitransaction data used for
shared row locks. Is there some process that can clean these two folder when
the lock is not needed?

Thanks.

best,
Ying



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/hot-standby-data-folder-bigger-than-primary-tp5800402p5800505.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] hot standby data folder bigger than primary

[Andres Freund]

Hi,

On 2014-04-17 06:28:52 -0700, yhe wrote:
> We are using streaming hot standby, no wal shipping. 
> 
> I checked and base and pg_xlog. both are fine. 
> 
> The extra space is used by /pg_multixact/members(about 5G) and
> /pg_multixact/offsets(about 1.5G)
> 
> The doc said that pg_multixact contains multitransaction data used for
> shared row locks. Is there some process that can clean these two folder when
> the lock is not needed?

Which version are you using? that might be recently (9.3.2 and
corresponding releases) fixed problem.

Greetings,

Andres Freund

-- 
 Andres Freund 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] Arduino SQL Connector

[Bruce Momjian]

On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:
> So, who wants to work on this with me? I'm a fair arduino programmer,
> but know nothing about postgres.

I would look at the MySQL one as a first step to see how that was done.
You are basically going to need to duplicate libpq, which is a major
undertaking.  There are some interfaces written in non-C languages like
Ruby and Java.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] Arduino SQL Connector

[David Rysdam]

Bruce Momjian  writes:
> On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:
>> So, who wants to work on this with me? I'm a fair arduino programmer,
>> but know nothing about postgres.
>
> I would look at the MySQL one as a first step to see how that was done.
> You are basically going to need to duplicate libpq, which is a major
> undertaking.  

Maybe I'm being naive, but isn't libpq already being compiled for ARM by
Debian? As long as it fits, you should be good. If it doesn't, you'll
need to strip some stuff out.


smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Arduino SQL Connector

[Bruce Momjian]

On Thu, Apr 17, 2014 at 10:44:36AM -0400, David Rysdam wrote:
> Bruce Momjian  writes:
> > On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:
> >> So, who wants to work on this with me? I'm a fair arduino programmer,
> >> but know nothing about postgres.
> >
> > I would look at the MySQL one as a first step to see how that was done.
> > You are basically going to need to duplicate libpq, which is a major
> > undertaking.  
> 
> Maybe I'm being naive, but isn't libpq already being compiled for ARM by
> Debian? As long as it fits, you should be good. If it doesn't, you'll
> need to strip some stuff out.

Oh, can you run Debian ARM code on Arduino?  If so, Postgres's libpq
could be used directly, though it is probably too big, as you mentioned.
The MySQL driver is C++, which surprised me.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] Arduino SQL Connector

[Adrian Klaver]

On 04/17/2014 07:44 AM, David Rysdam wrote:

Bruce Momjian  writes:

On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:

So, who wants to work on this with me? I'm a fair arduino programmer,
but know nothing about postgres.


I would look at the MySQL one as a first step to see how that was done.
You are basically going to need to duplicate libpq, which is a major
undertaking.


Maybe I'm being naive, but isn't libpq already being compiled for ARM by
Debian? As long as it fits, you should be good. If it doesn't, you'll
need to strip some stuff out.



If you get the MySQL connector from here:

https://launchpad.net/mysql-arduino/+milestone/release-1.0.2-beta

you will see they are doing the equivalent, using the MySQL libmysqlclient:

http://dev.mysql.com/downloads/connector/c/

--
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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Susan Cassidy]

I've never had that happen before, and I've used Perl and DBI a lot.

Susan


On Wed, Apr 16, 2014 at 5:34 PM, 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.
> 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 fully committed.
>
>
> On Wed, Apr 16, 2014 at 5:28 PM, David G Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> 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 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?
>>
>> Not by itself; but that factor could be interacting with something else to
>> cause the observed behavior.  As noted above functions are able to
>> maintain
>> their own "schema" environment so what is executed in one and outside of
>> one
>> can indeed target different physical objects - which has nothing to do
>> with
>> transaction visibility.
>>
>>
>> Susan Cassidy-3 wrote
>> > It is a fairly large and complex Perl program, so no, not really.
>>
>> Then you need to recreate a functionally similar, but limited, test case
>> that either exhibits the behavior in question or causes you to realize
>> what
>> you are doing in wrong in the "large and complex Perl program".
>>
>> David J.
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800459.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Susan Cassidy]

It is never committed, because the lookup for the insert fails.

Susan


On Wed, Apr 16, 2014 at 5:39 PM, David G Johnston <
david.g.johns...@gmail.com> wrote:

> 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 ID then this
> whole line of exploration (i.e., same session visibility) is pointless
> since
> we know beyond doubt committed data is visible to all other sessions.
>
> This would also further support the mistaken object identity theory I
> proposed up-thread.
>
> David J.
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800463.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.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] Arduino SQL Connector

[David Rysdam]

Bruce Momjian  writes:
> On Thu, Apr 17, 2014 at 10:44:36AM -0400, David Rysdam wrote:
>> Maybe I'm being naive, but isn't libpq already being compiled for ARM by
>> Debian? As long as it fits, you should be good. If it doesn't, you'll
>> need to strip some stuff out.
>
> Oh, can you run Debian ARM code on Arduino?  If so, Postgres's libpq
> could be used directly, though it is probably too big, as you mentioned.
> The MySQL driver is C++, which surprised me.

Well, I don't know about just picking it up and moving it over. But back
in the day I wrote a C library which I cross-compiled for ARM on my
Intel Linux box and used with my Arduino. At least, I think that's what
I did. This was 6 years ago.

The fact that it (presumably) compiles on Debian under ARM should mean
it would Just Cross-Compile for Arduino. Whether it would fit is another
question.


smime.p7s
Description: S/MIME cryptographic signature

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

[Susan Cassidy]

No, I am doing:
begin transaction
Loop:
Do some selects, including id on second iteration of the inserted id
Do the insert (function call), which also does a select on an id.
Save the newly inserted id for select on the next iteration.  This id will
be selected by the insert function on the next iteration, sometimes.  It is
being used the time that it fails.
end Loop;
do commit if the loop ever successfully gets done.
Susan



On Wed, Apr 16, 2014 at 5:49 PM, David G Johnston <
david.g.johns...@gmail.com> wrote:

> 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 actually
> > perform it until commit is issued.
>
> Wouldn't the same mechanism cache the corresponding SELECT?
>
>
> > 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 fully committed.
>
> Doubtful given the way most programs are coded (see assumption above) - the
> SELECT should be able to see the prior statement results whether committed
> or not.
>
> The only thing I can think of on this line-of-though is that auto-commit is
> off and while the original INSERT succeeded the transaction it was in was
> not "COMMIT"ed and the connection used closed/returned-to-pool with an
> implicit ROLLBACK.  Thus when the subsequent SELECT occurred the INSERT
> never appeared to happen.
>
> Not knowing the whether the ID is visible post-program-completion limits
> the
> ability to diagnose, though.
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800466.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.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] any way for a transaction to "see" inserts done earlier in the transaction?

[Susan Cassidy]

No.  One gets done automatically when the transaction fails, however.  I
can see it in the log.

Susan


On Wed, Apr 16, 2014 at 6:26 PM, Andy Colson  wrote:

> 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 > pie...@hogranch.com>> 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 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 connection until its committed.
>>
>> --
>> john r pierce  37N 122W
>> somewhere on the middle of the left coast
>>
>>
>
> Any chance you accidentally fired of a rollback?
>
> -Andy
>
>

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

[Susan Cassidy]

There aren't multiple connections.  It is a CGI program.  One connection is
made when the program starts, and that is all.

I've looked at the log.  It shows just what I expect, except that the
lookup does not work.  0 rows are returned from the select of the newly
inserted id.

Susan




On Wed, Apr 16, 2014 at 7:32 PM, Adrian Klaver wrote:

> 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 program find it.
>>
>
> To expand on my earlier post and to address all the different scenarios
> proposed, it might to be a good idea to expand on what you are logging. The
> caveat is whether you are working against a production server or a
> development. In the development case you would presumably be able to more
> easily limit the scope of what you are observing. In that case turning up
> the log_statement to 'all' in postgresql.conf would give you a picture of
> what is actually hitting the database. So you could see if there where
> multiple connections happening or multiple transactions in a connection or
> if different tables where being used, etc. That would help create some
> anchor points from which you could backwards engineer to possible causes.
>
>
>> Susan
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Susan Cassidy]

I suppose it's possible.  I've never seen this behavior before, but I don't
think I've ever used this same scenario before.  It is slightly unusual.

Susan


On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys  wrote:

> 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 likely, or if it did it wouldn't be able to know what id was returned
> from the function (which calls nextval(), but that isn't relevant here
> since it's marked volatile).
> That makes it a possible scenario for what's being witnessed here.
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>
> --
> 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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Susan Cassidy]

Except for the fact that I get the new id returned from the first insert,
which means that the insert probably did happen.

Susan


On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys  wrote:

> 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 likely, or if it did it wouldn't be able to know what id was returned
> from the function (which calls nextval(), but that isn't relevant here
> since it's marked volatile).
> That makes it a possible scenario for what's being witnessed here.
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>
> --
> 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] Arduino SQL Connector

[Jan Wieck]

On 04/17/14 10:49, Bruce Momjian wrote:

On Thu, Apr 17, 2014 at 10:44:36AM -0400, David Rysdam wrote:

Bruce Momjian  writes:
> On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:
>> So, who wants to work on this with me? I'm a fair arduino programmer,
>> but know nothing about postgres.
>
> I would look at the MySQL one as a first step to see how that was done.
> You are basically going to need to duplicate libpq, which is a major
> undertaking.

Maybe I'm being naive, but isn't libpq already being compiled for ARM by
Debian? As long as it fits, you should be good. If it doesn't, you'll
need to strip some stuff out.


Oh, can you run Debian ARM code on Arduino?  If so, Postgres's libpq
could be used directly, though it is probably too big, as you mentioned.
The MySQL driver is C++, which surprised me.


No, to do that you'd need something like a Beaglebone, which is ARM 
Cortex A8 based and runs Linux anyway.


http://www.ti.com/tool/beaglebk?DCMP=PPC_Google_TI&k_clickid=63c22498-5f5d-3789-4b41-0dabd35d

I don't think porting the whole libpq over to an Arduino would be a good 
move. For practical purposes a small subset of functionality through 
some gateway service would probably be a better approach.


Note that I am not an Arduino user/developer. I'm more familiar with the 
Microchip PICs.



73 de WI3CK

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] hot standby data folder bigger than primary

[yhe]

hi, Andy,

Thanks. Right we are using 9.2.4 but plan to upgrade soon. I read 9.3.2
release notes and it is fixed specifically for this.

best,
Ying



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/hot-standby-data-folder-bigger-than-primary-tp5800402p5800545.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Arduino SQL Connector

[David Rysdam]

Steve Spence  writes:
> no, you can't run arm / debian on an arduino UNO. it's all c++
> compiled to machine code then uploaded.

This is how all executables work. 


smime.p7s
Description: S/MIME cryptographic signature

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

[Scott Marlowe]

So any chance of a self-contained test case so we're not all chasing our tails?

On Thu, Apr 17, 2014 at 9:06 AM, Susan Cassidy
 wrote:
> Except for the fact that I get the new id returned from the first insert,
> which means that the insert probably did happen.
>
> Susan
>
>
> On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys  wrote:
>>
>> 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 likely, or if it did it wouldn’t be able to know what id was returned
>> from the function (which calls nextval(), but that isn’t relevant here since
>> it’s marked volatile).
>> That makes it a possible scenario for what’s being witnessed here.
>>
>> Alban Hertroys
>> --
>> If you can't see the forest for the trees,
>> cut the trees and you'll find there is no forest.
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>



-- 
To understand recursion, one must first understand recursion.


-- 
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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Robert DiFalco]

Right. I don't know this code or DBI but many frameworks create a pool of ids 
using sequence generators so that they can minimize round trips and know the id 
of new records before the are written. 

Sent from my iPhone

> On Apr 17, 2014, at 8:06 AM, Susan Cassidy 
>  wrote:
> 
> Except for the fact that I get the new id returned from the first insert, 
> which means that the insert probably did happen.  
> 
> Susan
> 
> 
>> On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys  wrote:
>> 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 likely, or if it did it wouldn’t be able to know what id was returned 
>> from the function (which calls nextval(), but that isn’t relevant here since 
>> it’s marked volatile).
>> That makes it a possible scenario for what’s being witnessed here.
>> 
>> Alban Hertroys
>> --
>> If you can't see the forest for the trees,
>> cut the trees and you'll find there is no forest.
>> 
>> 
>> 
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
> 

Re: [GENERAL] pg_upgrade & tablespaces

[Bruce Momjian]

On Mon, Jan 13, 2014 at 09:17:41AM -0500, Joseph Kregloh wrote:
> 
> 
> 
> On Fri, Jan 10, 2014 at 11:53 AM, Adrian Klaver 
> wrote:
> 
> On 01/10/2014 08:40 AM, Joseph Kregloh wrote:
> 
> Just as a followup to this. The process that I am using to do the
> upgrade is as follows:
> 
> 1. Install Postgres 9.3 in /opt dir.
> 2. In 9.0 instance update spclocation in pg_tablespace.
> 3. Update the symlinks in the pg_tblspace folder.
> 4. Move the tablespace folders to new location.
> 5. Run pg_upgrade.
> 6. Test upgrade results on 9.3 cluster.
> 7. Run the sh files created by pg_upgrade.
> 8. Uninstall Postgres 9.3 in /opt dir.
> 9. Install Postgres 9.3 in default location.
> 10. Enjoy Postgres 9.3.
> 
> 
> For completeness, the new location you are moving the tablespaces to, is
> that in or out of $PGDATA?

This will be fixed in the next 9.3 minor release by throwing ane error
for non-existent tablespace directores.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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

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

[David G Johnston]

Please note that everyone here but you is bottom-posting; please follow the
convention and list standard.


Susan Cassidy-3 wrote
> It is never committed, because the lookup for the insert fails.

So, alter the code so only the first insert happens then stop further
processing and go explore that state of the database.

Or, add a "commit" after the initial insert so at least that is memorialized
for future research even if the rest of the code fails.

You are going to have to do some leg-work here; a pure thought experiment is
not going to cut it.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800567.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] any way for a transaction to "see" inserts done earlier in the transaction?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Bosco Rama wrote:
> Is it returning the right id?  I seem to remember a recent thread
> about Perl DBI returning the wrong id's for certain operations.

Er...can you point me to that thread, please? I'd be very interested 
in such a bug.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201404171231
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlNQAe8ACgkQvJuQZxSWSshObwCglozBhwT4ddf7XacITKlucaou
7iEAoOXyK+SvYn5dBiBnrLyAkmzvDyB+
=SrCC
-END PGP SIGNATURE-




-- 
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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Susan Cassidy]

I don't see how.  It is a fairly complicated program, and the perl calls
are done through an API, which works fine in all other circumstances (I was
told I had to use an API, and not use the Perl calls directly).

I moved the code in the function inline into the code, and I still cannot
find the newly inserted id the next time through the loop.  I think I'm
just going to have to commit each time through the loop, although I really
hate to.  Maybe I can keep a list of the newly inserted rows, and delete
them if anything goes wrong later in the loop.

Thanks,
Susan


On Thu, Apr 17, 2014 at 8:13 AM, Scott Marlowe wrote:

> So any chance of a self-contained test case so we're not all chasing our
> tails?
>
> On Thu, Apr 17, 2014 at 9:06 AM, Susan Cassidy
>  wrote:
> > Except for the fact that I get the new id returned from the first insert,
> > which means that the insert probably did happen.
> >
> > Susan
> >
> >
> > On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys 
> wrote:
> >>
> >> 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 likely, or if it did it wouldn't be able to know what id was
> returned
> >> from the function (which calls nextval(), but that isn't relevant here
> since
> >> it's marked volatile).
> >> That makes it a possible scenario for what's being witnessed here.
> >>
> >> Alban Hertroys
> >> --
> >> If you can't see the forest for the trees,
> >> cut the trees and you'll find there is no forest.
> >>
> >>
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> >
>
>
>
> --
> To understand recursion, one must first understand recursion.
>

Re: [GENERAL] Arduino SQL Connector

[John R Pierce]

On 4/17/2014 7:46 AM, Steve Spence wrote:

Not using arm or debian. Using a atmel 328p.


which is an AVR processor architecture.   16 bit, limited memory space.

you should be able to compile libpq as a static linkable library if you 
have an AVR compiler, its pure C.   I would leave out SSL support if you 
can, as openssl is a mess.



--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
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] Arduino SQL Connector

[Andy Colson]

On 4/17/2014 9:44 AM, David Rysdam wrote:

Bruce Momjian  writes:

On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:

So, who wants to work on this with me? I'm a fair arduino programmer,
but know nothing about postgres.


I would look at the MySQL one as a first step to see how that was done.
You are basically going to need to duplicate libpq, which is a major
undertaking.


Maybe I'm being naive, but isn't libpq already being compiled for ARM by
Debian? As long as it fits, you should be good. If it doesn't, you'll
need to strip some stuff out.



Yeah, agreed, no need to rewrite it.  Just cross compile it.  Even if 
you cant use the Debian build, you are already cross compiling stuff, 
right?


-Andy


--
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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Vincent Veyron]

On Thu, 17 Apr 2014 10:02:00 -0700
Susan Cassidy  wrote:

> I moved the code in the function inline into the code, and I still cannot
> find the newly inserted id the next time through the loop.  

I suppose you use DBD::Pg, whose current default isolation transaction level is 
``Serializable'' 

Don't know if it applies, but the Postgresql's documentation says this :

SERIALIZABLE

All statements of the current transaction can only see rows committed 
before the first query or data-modification statement was executed in this 
transaction. If a pattern of reads and writes among concurrent serializable 
transactions would create a situation which could not have occurred for any 
serial (one-at-a-time) execution of those transactions, one of them will be 
rolled back with a serialization_failure SQLSTATE. 

-- 
Regards, Vincent Veyron 

http://libremen.com/ 
Legal case, contract and insurance claim management software


-- 
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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Susan Cassidy]

I found the problem, and it is all my fault.  I was calling the insert
function with the wrong combination of parameters, so naturally it didn't
find the item.  It is working fine now, although I do think I needed to
mark the function as VOLATILE, which I think helped.

Thanks to all for the help.

Susan


On Thu, Apr 17, 2014 at 10:33 AM, Vincent Veyron <
vincent.vey...@libremen.org> wrote:

> On Thu, 17 Apr 2014 10:02:00 -0700
> Susan Cassidy  wrote:
>
> > I moved the code in the function inline into the code, and I still cannot
> > find the newly inserted id the next time through the loop.
>
> I suppose you use DBD::Pg, whose current default isolation transaction
> level is ``Serializable''
>
> Don't know if it applies, but the Postgresql's documentation says this :
>
> SERIALIZABLE
>
> All statements of the current transaction can only see rows committed
> before the first query or data-modification statement was executed in this
> transaction. If a pattern of reads and writes among concurrent serializable
> transactions would create a situation which could not have occurred for any
> serial (one-at-a-time) execution of those transactions, one of them will be
> rolled back with a serialization_failure SQLSTATE.
>
> --
> Regards, Vincent Veyron
>
> http://libremen.com/
> Legal case, contract and insurance claim management software
>

Re: [GENERAL] pg_upgrade & tablespaces

[Joseph Kregloh]

> This will be fixed in the next 9.3 minor release by throwing ane error
> for non-existent tablespace directores.
>
>
Awesome! I have already upgraded my dev, stage, preprod, and production
environments to 9.3. However I do have some snapshots that I can test with.


> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
>   + Everyone has their own god. +
>

Re: [GENERAL] shared memory allocation - C function

[Alan Nilsson]

Perfect, thank you.

On Apr 16, 2014, at 9:56 PM, Michael Paquier  wrote:

> 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 
> accessible in a c function from any process.  Is there an SPI/API to do this?
> You can allocate a custom amount of shared memory at server initialization 
> using the hook called shmem_startup_hook in ipci.c through a library loaded 
> with shared_preload_libraries. Why not having a look at 
> contrib/pg_stat_statements which is a good example?
> -- 
> Michael

[GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Guillaume Drolet]

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
 to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.

I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.

Best regards and many thanks for your advice,

Guillaume


-- 
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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Vincent Veyron]

On Thu, 17 Apr 2014 10:02:00 -0700
Susan Cassidy  wrote:

> I moved the code in the function inline into the code, and I still cannot
> find the newly inserted id the next time through the loop.  

I suppose you use DBD::Pg, whose current default isolation transaction level is 
``Serializable'' 

Don't know if it applies, but the Postgresql's documentation says this :

SERIALIZABLE

All statements of the current transaction can only see rows committed 
before the first query or data-modification statement was executed in this 
transaction. If a pattern of reads and writes among concurrent serializable 
transactions would create a situation which could not have occurred for any 
serial (one-at-a-time) execution of those transactions, one of them will be 
rolled back with a serialization_failure SQLSTATE. 

-- 
Regards, Vincent Veyron 

http://libremen.com/ 
Legal case, contract and insurance claim management software


-- 
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] Re: any way for a transaction to "see" inserts done earlier in the transaction?

[Vincent Veyron]

My apologies to all, I posted with the wrong id; reposting

-- 
Regards, Vincent Veyron 

http://libremen.com/ 
Legal case, contract and insurance claim management software


-- 
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] Arduino SQL Connector

[Tomas Vondra]

On 17.4.2014 16:51, Adrian Klaver wrote:
> On 04/17/2014 07:44 AM, David Rysdam wrote:
>> Bruce Momjian  writes:
>>> On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:
 So, who wants to work on this with me? I'm a fair arduino programmer,
 but know nothing about postgres.
>>>
>>> I would look at the MySQL one as a first step to see how that was done.
>>> You are basically going to need to duplicate libpq, which is a major
>>> undertaking.
>>
>> Maybe I'm being naive, but isn't libpq already being compiled for ARM by
>> Debian? As long as it fits, you should be good. If it doesn't, you'll
>> need to strip some stuff out.
>>
> 
> If you get the MySQL connector from here:
> 
> https://launchpad.net/mysql-arduino/+milestone/release-1.0.2-beta
> 
> you will see they are doing the equivalent, using the MySQL libmysqlclient:
> 
> http://dev.mysql.com/downloads/connector/c/

Really? I see no reference to libmysqlclient there. It seems they're
simply reimplementing only a (small) subset of the library.

Tomas


-- 
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] Arduino SQL Connector

[Tomas Vondra]

On 17.4.2014 19:43, Steve Spence wrote:
> Oracle thought it was a good idea to put out a MySQL version, I
> figure there should be some effort to counter that here .

Really? I found no information about this on oracle.com or mysql.com,
except for a section in the discussion forum with ~20 posts. It's true
that the author is apparently a Senior Software Engineer at Oracle, but
this seems like a his own project. So much regarding the "Oracle
thought" part ...

BTW I doubt the approach "Oracle does X => you should do X too" will be
fruitful here.

> Need a Team lead on this, and I'll collaborate as much as I can on the
> Arduino / Networking side.

I'm a bit confused. What do you mean by "team lead"? I was thinking that
maybe this would be a fun project for the holidays, refreshing my
Arduino-fu. But no way I'm interested in being a TL of anything.

I was messing with the PostgreSQL protocol [1] recently, and writing a
code implementing something like what the mysql connector does would be
quite trivial. A day of work, maybe two if you know networking but not
the protocol.

regards
Tomas

[1] http://www.postgresql.org/docs/9.3/interactive/protocol.html


-- 
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] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Adrian Klaver]

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
 to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.


You do not use initdb to add superusers, that can be done with 
createuser from the command line or CREATE ROLE via SQL. The -U option 
just establishes the first superuser and is not even necessary as it 
defaults to the system user running initdb.



Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).


Are you sure you recovered the entire directory?



Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.


Well 937 is the version for 9.3. The version number increases with time, 
but I am pretty sure Postgres is no where near 16795209, so this does 
not look promising :( At a guess either the delete happened while the 
cluster was running and a file or files where corrupted or the data 
recovery was not entirely successful.




I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.


Do you have a backup of your main cluster?



Best regards and many thanks for your advice,

Guillaume





--
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] Arduino SQL Connector

[Adrian Klaver]

On 04/17/2014 03:04 PM, Tomas Vondra wrote:

On 17.4.2014 16:51, Adrian Klaver wrote:

On 04/17/2014 07:44 AM, David Rysdam wrote:

Bruce Momjian  writes:

On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:

So, who wants to work on this with me? I'm a fair arduino programmer,
but know nothing about postgres.


I would look at the MySQL one as a first step to see how that was done.
You are basically going to need to duplicate libpq, which is a major
undertaking.


Maybe I'm being naive, but isn't libpq already being compiled for ARM by
Debian? As long as it fits, you should be good. If it doesn't, you'll
need to strip some stuff out.



If you get the MySQL connector from here:

https://launchpad.net/mysql-arduino/+milestone/release-1.0.2-beta

you will see they are doing the equivalent, using the MySQL libmysqlclient:

http://dev.mysql.com/downloads/connector/c/


Really? I see no reference to libmysqlclient there. It seems they're
simply reimplementing only a (small) subset of the library.


My mistake, was reading to fast and assumed facts not in evidence.


Tomas





--
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] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Adrian Klaver]

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
 to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.

I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.


Further investigation found pg_resetxlog:

http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html

I have never used it, so all I can do is point to the docs. I would say 
that if you go this route make a copy of your $PGDATA in another 
location for safe keeping in the event things go wrong.





Best regards and many thanks for your advice,

Guillaume





--
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] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

[Adrian Klaver]

On 04/17/2014 01:29 PM, Guillaume Drolet wrote:

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D
 to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.



More thoughts.

What happened to the new cluster?

Is it still running?

When you got the error messages where you pointing at the old or new 
cluster?




I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.

Best regards and many thanks for your advice,

Guillaume





--
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] Unrecognized service

[Augori]

Hi Adrian,

I don't know the answer to #1. Was that a wildcard search?

As for #2, I should have been more clear, that's not a check that it does
every night.  I just ran that check when it seemed to be down.  It has been
a long time to since I've worked with it, so I didn't correctly recall that
I needed to look for postgresql-9.2 and not just postgresql.   The problem
came up because the nightly process (a Python script that uses psycopg2)
tried to, but couldn't connect to postgresql-9.2 server.

Does that make more sense?


On Tue, Apr 15, 2014 at 12:08 AM, Adrian Klaver
wrote:

> On 04/14/2014 08:25 PM, Augori wrote:
>
>> Hi all,
>>
>> woohoo!
>>
>> service postgresql-9.2 status
>>
>> (pid  9924) is running...
>>
>>
>>It seems that I was looking for the service by the wrong name, as
>> John guessed correcty.Also, Tom, it's good to know that the data
>> won't necessarily go away if I need to reinstall at some point.
>>
>
> Well that still leaves two questions unanswered.
>
> 1) Why did the postgres process not show up in the ps ax output?
>
> 2) Why is the nightly process doing a status check on postgresql not
> postgresql-9.2 ?
>
> From the original post:
>
>
> # service postgresql status
> postgresql: unrecognized service
>
>
>
>
>> thank you so much for the messages from all three of you.  Your rapid
>> responses were very encouraging.
>>
>>
>>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Unrecognized service

[Adrian Klaver]

On 04/17/2014 05:55 PM, Augori wrote:

Hi Adrian,

I don't know the answer to #1. Was that a wildcard search?


It was a grep of the processes running. You replied with this:

"Here's what the ps command gives:

root(at)server# ps ax | grep post
 9165 pts/1S+ 0:00 grep post"

If postgres was running it should have shown up above. Just seemed to be 
at odds with later post:


"
woohoo!

service postgresql-9.2 status

(pid  9924) is running...
"

All I can figure is at some point between the first and second post the 
service for Postgres was started again. There just was no mention of 
that so I was trying to figure out the sequence of events.




As for #2, I should have been more clear, that's not a check that it
does every night.  I just ran that check when it seemed to be down.  It
has been a long time to since I've worked with it, so I didn't correctly
recall that I needed to look for postgresql-9.2 and not just postgresql.
   The problem came up because the nightly process (a Python script that
uses psycopg2) tried to, but couldn't connect to postgresql-9.2 server.

Does that make more sense?


Sort of, if the Python script is the only thing hitting the database. 
Otherwise I would have expected other 'users' to notice the database was 
down. Furthermore, psycopg2 does not know service names, it connects to 
a port, host, database as a user. So unless those where changed I would 
expect it to keep on connecting, unless the service was stopped, which 
seems to follow what I mention above. At any rate everything worked out.





On Tue, Apr 15, 2014 at 12:08 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 04/14/2014 08:25 PM, Augori wrote:

Hi all,

woohoo!

service postgresql-9.2 status

(pid  9924) is running...


It seems that I was looking for the service by the wrong
name, as
John guessed correcty.Also, Tom, it's good to know that the data
won't necessarily go away if I need to reinstall at some point.


Well that still leaves two questions unanswered.

1) Why did the postgres process not show up in the ps ax output?

2) Why is the nightly process doing a status check on postgresql not
postgresql-9.2 ?

 >From the original post:


# service postgresql status
postgresql: unrecognized service




thank you so much for the messages from all three of you.  Your
rapid
responses were very encouraging.



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





--
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] Arduino SQL Connector

[John R Pierce]

On 4/17/2014 9:09 PM, Steve Spence wrote:

You know what? Fine, it doesn't matter that much to me. I'm happy to
continue using MySQL. It works with the Arduino quite nicely. Postgres
doesn't work. That's Postgres loss not mine. I really thought the
postgres team would be interested in providing support for a very
popular microcontroller system. If not, they are missing out on a
larger segment of users.


personal opinion:

I don't think a terminal device like a PC or an embedded system should 
be talking directly to SQL at all.   instead, they should be talking to 
an application server which implements the "business logic", and THAT 
talks to the database.





--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
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] Arduino SQL Connector

[Jan Wieck]

On 04/18/14 00:27, Steve Spence wrote:

On 4/18/2014 12:21 AM, John R Pierce wrote:


personal opinion:

I don't think a terminal device like a PC or an embedded system should
be talking directly to SQL at all.   instead, they should be talking
to an application server which implements the "business logic", and
THAT talks to the database.





When all we need to do is log sensor values, there's no business logic
needed. It's scientific data collection, and direct to SQL works very
nicely. It's fast and clean.


In that case you should be sending messages to a message bus or queue.

The bus/queue receiver will then push the data into the database or 
whatever downstream system.



Jan


--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] Arduino SQL Connector

[Gavin Flower]

On 18/04/14 16:27, Steve Spence wrote:

On 4/18/2014 12:21 AM, John R Pierce wrote:


personal opinion:

I don't think a terminal device like a PC or an embedded system 
should be talking directly to SQL at all.   instead, they should be 
talking to an application server which implements the "business 
logic", and THAT talks to the database.





When all we need to do is log sensor values, there's no business logic 
needed. It's scientific data collection, and direct to SQL works very 
nicely. It's fast and clean.



If you are just logging sensor values, why bother with a database?

I share John's point of view.  If you find a database useful, then 
architectures that shield the database from external systems and users 
is preferred for a number of reasons - such as giving you far more 
flexbility for adding additional functionality in the future, and 
providing better security.  Any processing of scientific data is 
considered "business logic", in this context. Simply because people use 
MySQL in a dubious ways (there are many problems in MySQL anyway, that 
make me want to cringe), does not mean they are practices to be 
encouraged by responsible software developers.


So if you are serious about your data integrity, and really think a 
database is appropriate, then I would definitely recommend PostgreSQL 
over MySQL.  I have done programming and DBA work with MySQL, but I 
would never recommend it (even before Oracle got their hands on it!) for 
a new project.


Cheers,
Gavin





--
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] Approach to Data Summary and Analysis

[Roxanne Reid-Bennett]

On 4/16/2014 2:40 PM, Robert DiFalco wrote:
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 timestamp to compare it against today or do an exist 
query to see if any timestamp for that user is greater or equal than 
"today".


I didn't go back to look at your original schema- but.. if your 500K 
records are coming in time ordered... You may be able to track "max" as 
an attribute  on an "SCD" based on the caller/callee table [or the 
caller/ee table itself if that table is only used by your app] with an 
update from a post-insert trigger on the appropriate table. Even if they 
aren't time ordered, you add the overhead of a single comparative in the 
trigger.  Downside is that you fire a trigger and an update for every 
insert. [or just an update depending on what is driving your load of the 
500K records]


Again - the proof on "value" of this overhead is a comparison of the 
cost for the updates vs the cost on the query to find max()  I suspect 
your once a day query can afford all sorts of other optimizations that 
are "better" than a trigger fired on every insert.  [such as the 
function index - that was already mentioned] I really suspect you just 
don't have enough load on the query side (complex queries * # of users) 
to justify the extra load on the write side (+1 trigger, +1 update / 
insert) to avoid a (potentially) heavy query load 1x/day.


Another option... if only worried about "today".. then keep only 
"today's" data in your query table, and migrate historical data nightly 
to a pseudo archive table for those "every once in a while" questions.  
I haven't played with table inheritance in Postgres - but that's a 
capability I might look at if I were doing a pseudo archive table.


Roxanne



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