Re: [GENERAL] how to update structural & data changes between PostgreSQL

We have a fairly 'good' process at our shop that we follow that works for us First we do a schema comparison between our prod and devl/test environments using the EMS PostgreSQL database comparer tool... We extract the DDL changes that it produces ("alter table add column, etc") and place

Re: [GENERAL] Replication & web apps

async replication, the slave nodes are only used as backups or for read-only reporting systems) Jeff Amiel wrote: Well, you've hit upon the difference between synchronous replication and asynchronous replication (and touched on the idea of clustering/load balancing) In a synchronous replic

Re: [GENERAL] Replication & web apps

sit in the JDBC layer and direct traffic and control the load balancing. Jeff Amiel Leonardo Francalanci wrote: Hi, I still don't understand how replication can be used in web applications. Given this scenario: 1) user updates his profile -> update to the db (master) 2) web app redirec

Re: [GENERAL] Triggers and Audit Trail

We (my company) never found a way. We ended up writing java code that analyzed the catalog tables that generated the appropriate 'if' statements in the trigger functions for us Actuallywe tinkered with hitting the catalog tables inside our triggers, but for performance reasons, we

Re: [GENERAL] Triggers and Audit Trail

Using triggers, is there a way to loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field name and value that triggered the update other than hard coding if statements to compare every field of the OLD and NEW records. We (my company) never found a

Re: [GENERAL] How to compare the schemas ?

I paid $69 and purchased the EMS PostgreSQL DB Comparer tool. Works great for me.. They have a trial version you can play with... They even have a command line version.. http://www.sqlmanager.net/en/products/postgresql/dbcomparer Peter Fein wrote: See also the recent thread 'Version Co

Re: [GENERAL] Persistent data per connection

Steve Atkins wrote: Using a temporary table to store that unique token is how I'd do it. Rather than grovel through the system tables during the trigger I'd do all the work at the client session setup. As the first thing, write the token into the temporary table. If that fails, create the temporary

Re: [GENERAL] Persistent data per connection

Steve Atkins wrote: On Fri, Mar 25, 2005 at 10:56:50AM -0600, Jeff Amiel wrote: because the connection is never really dropped... using a connection poolso it's just reclaimed by the pool on a connection.close() or after a timeout period Then you don't really want per-

Re: [GENERAL] Persistent data per connection

because the connection is never really dropped... using a connection poolso it's just reclaimed by the pool on a connection.close() or after a timeout period Tom Lane wrote: Jeff Amiel <[EMAIL PROTECTED]> writes: We've been struggling for several days now to come up

[GENERAL] Persistent data per connection

We've been struggling for several days now to come up with a mechanism that allows us to establish a mechanism to store data that remains persistent for the life of the connection. Essentially we have a web based application that utilizes a connection pool (using one single 'super' postgresql d

Re: [GENERAL] Converting from single user w/pool to multiple users

We intended to do that very thing (read the earlier parts of the thread between myself and Tom Lane) Jeff Guy Rouillier wrote: Jeff Amiel wrote: The issue is really propogating the authenticaion credentials to the database itself.it's our ON INSERT/ON UPDATE/ON DELETE triggers tha

Re: [GENERAL] Converting from single user w/pool to multiple users

Guy Rouillier wrote: Do you really want to try to administer 1000s of database user accounts in addition to whereever you maintain these same accounts for non-DB authentication. This assumes, of course, that if you are interested in accountability at all that you authenticate somewhere. And I cer

Re: [GENERAL] Converting from single user w/pool to multiple users

Could we continue to use our existing connection pool (via our app server) and every time the application 'gets' a connection (as a superuser) , we then 'SET SESSION AUTHORIZATION' to the appropriate user who is performing the action? Under the new scheme, we are limited by max_connections

[GENERAL] Converting from single user w/pool to multiple users

Web based application that utilizes PostgreSQL (7.45 currently). A debate is raging in the office regarding the idea of switching from using a connection pool (that utilizes a single god-like database user) to a model where each web user would have a mirror postgresql user. All connections to th

Re: [GENERAL] skip weekends: revisited

Andre Maasikas wrote: From the looks of it, variable date seems to calculate the first day of a week and for it to work day_of_the_week has to start from 0 so Sunday - 0, Friday - 5 (in US at least?) Andre That appears to be the case (0 is sunday.) thanks for the assist!! -

[GENERAL] skip weekends: revisited

Back in 2002, Jean-Luc Lachance gave a nifty algorithm (http://archives.postgresql.org/pgsql-sql/2002-06/msg00301.php) for determining the resulting date given a starting date and number of 'workdays' in the future. The trick was that weekends (Saturday and Sunday) could not be counted. The a

Re: [GENERAL] PostgreSQL still for Linux only?

While we run PostgreSQL on Free-BSD for our production systems, we have 'demo' laptop windows XP systems that contain the entire server architecture (application server, database, win32 client, etc). Sure is handy to be able to run PostgreSQL on windows and not have to change anything.. P

[GENERAL] Schema comparison tool

I'm looking specifically for a tool to help compare 2 database schemas (nominally, production and development) and generate the appropriate SQL (that can be later executed) to bring the to schema's into sync. Option1 : pg_dump -s firstdatabasename > first pg_dump -s seconddatabasename > second

Re: [GENERAL] Dont let those int8's drive you mad!!

Outstanding thanks all Tom Lane wrote: Jeff Amiel <[EMAIL PROTECTED]> writes: If postgres knows the field is an int8, why do I have to cast it in my query? As of 8.0 you won't have to anymore. You don't really want to know why it took us six years to find a w

[GENERAL] Dont let those int8's drive you mad!!

This HAS bitten me before. Questions: If postgres knows the field is an int8, why do I have to cast it in my query? Any way I can avoid having to watch for this particular column (and 3 others in other tables) column in all my queries? -- Jeff Amiel Systems/Development Manager iStre

Re: [GENERAL] Certifications in military environment

Not to question your data, but where did the info about the navy and army using it come from? I saw nothing on the advocacy site nor did any google search for press releases come up with anything.I'd actually love to know how/where they are using it... Joshua D. Drake wrote: PostgreSQL is

Re: [GENERAL] using database for queuing operations?

this time, I've done an unncessary extra select to see if the value has changed because of competing processes trying to work on same row Once again...kudos to lists like this one and of course, RTFM. Jeff Tom Lane wrote: Jeff Amiel <[EMAIL PROTECTED]> writes: "the docs say

Re: [GENERAL] postgres book

I used this book as my intro and my guide in my first few months running postgresql I thought it was super informative...had lots of real usable infoand I still occasionally refer to it... Jeff Greg Donald wrote: I'm thinking of getting this book and was wondering if anyone had anything

Re: [GENERAL] postgres "on in the internet"

Well...if it's truly a client/server app, why not have the client app talk to the server who actually executes the SQL. That way no remote access to the database is required. IMHO, it's a 'bad thing' to have your database exposed to the internet Jeff Amiel Paul Ti

Re: [GENERAL] upgrading minor versions

thanks for the heads up about the pg_config stuff... I wondered about the difference.I have several boxes that were installed via ports (which have the binaries reside in /usr/local/pgsql) and some hand installed from the tar (which are in usr/local/bin). At least that explains why I have

Re: [GENERAL] upgrading minor versions

Duh. I never restarted the server. sheesh. thanks to all. Jeff Doug McNaught wrote: Did you shut down and restart the server after the upgrade? Are you sure the new binaries went where you think they did? -Doug ---(end of broadcast)--- TIP 4: Do

Re: [GENERAL] upgrading minor versions

Ok..still no dice. still shows 7.4.2 when I select version()... I must surely be doing something silly here... Jan Wieck wrote: On 8/27/2004 12:21 PM, Jeff Amiel wrote: This may sound like a silly question I am currently running 7.4.2. I would like to upgrade to 7.4.5... So I downloaded

[GENERAL] upgrading minor versions

This may sound like a silly question I am currently running 7.4.2. I would like to upgrade to 7.4.5... So I downloaded postgresql-7.4.5.tar.gz uncompressed... configure... make make install I run psql and it gives m

Re: [GENERAL] pg_stat_activity versus ps

That was it. (not having it turned on. duh). Guess I should have read section 23.2 of the docs..."The Statistics Collector" Thanks for the heads up. Jeff Tom Lane wrote: Jeff Amiel <[EMAIL PROTECTED]> writes: however, if I checked pg_stat_activity during the same time period

[GENERAL] pg_stat_activity versus ps

I ran a home-grown self continuous stress test tool against my 7.4.2 database. I banged 'ps' (running freebsd) while it was active and witnessed several of the 'back end' postgres processes exeucting queries, commits, inserts, etc(the actual work the processes were doing was listed in the

<    1   2