Re: [GENERAL] Locking question

- Original Message - > From: "Frank Millman" > To: pgsql-general@postgresql.org > Sent: Wednesday, October 26, 2016 4:42:29 AM > Subject: [GENERAL] Locking question > Hi all > I am designing an inventory application, and I want to ensure that the stock > level of any

[GENERAL] pg_dumpall problem when roles have default schemas

I've run into a problem while migrating an existing 8.2.7 data base to a new server running 8.3.3 (although I think the version numbers may not matter -- I think I've seen this problem in the past and just lived with it since so much of Postgresql is so great!). The problem stems from the fact

[GENERAL] pg_dumpall problem when roles have default schemas

I've run into a problem while migrating an existing 8.2.7 data base to a new server running 8.3.3 (although I think the version numbers may not matter -- I think I've seen this problem in the past and just lived with it since so much of Postgresql is so great!). The problem stems from the

Re: [GENERAL] pg_dumpall problem when roles have default schemas

Tom Lane wrote: [EMAIL PROTECTED] writes: Thus, when piping the output (from legacy host 192.168.2.2) to populate the newly initialized cluster, by way of running (on the new host 192.168.2.3) pg_dumpall -h 192.168.2.2|psql an error occurs in that first section when the script

Re: [GENERAL] pg_dumpall problem when roles have default schemas

Tom Lane wrote: [EMAIL PROTECTED] writes: Thus, when piping the output (from legacy host 192.168.2.2) to populate the newly initialized cluster, by way of running (on the new host 192.168.2.3) pg_dumpall -h 192.168.2.2|psql an error occurs in that first section when the

[GENERAL] pg_dumpall problem when roles have default schemas

I've run into a problem while migrating an existing 8.2.7 data base to a new server running 8.3.3 (although I think the version numbers may not matter -- I think I've seen this problem in the past and just lived with it since so much of Postgresql is so great!). The problem stems from the

Re: [GENERAL] How to remove duplicate lines but save one of the lines?

A B wrote: I have a table with rows like this A 1 A 1 B 3 B 3 C 44 C 44 and so on. and I want it to be A 1 B 3 C 44 so how can I remove the all the duplicate lines but one? CREATE TEMP TABLE tmp AS SELECT DISTINCT * FROM t1; DROP TABLE t1; CREATE TABLE t1 AS SELECT * FROM tmp; -- Sent

Re: [GENERAL] Is there an md5sum for tables?

Michael Enke wrote: I need to know if multiple tables have (may have most probably) identical content. Since I want a fast solution (which means not comparing tables row by row), I thought it would be a good idea to have an sql function operating on a table or view similar to md5sum on a file

Re: [GENERAL] Trigger to run @ connection time?

Kynn Jones wrote: On Tue, Mar 11, 2008 at 5:28 PM, Tom Lane [EMAIL PROTECTED] wrote: Kynn Jones [EMAIL PROTECTED] writes: If one can set up this insert operation so that it happens automatically whenever a new connection is made, I'd like to learn how it's done. For manual psql sessions, you

Re: [GENERAL] how do you write aggregate function

Richard Broersma wrote: On Tue, Mar 4, 2008 at 8:44 AM, Justin [EMAIL PROTECTED] wrote: I searched the archive of the mail list and did not find anything Search the documentation. There are a couple great examples posted at

Re: [GENERAL] keeping 3 tables in sync w/ each other

Ow Mun Heng wrote: Hi, I have 3 tables foo foo_loading_source1 foo_loading_source2 which is something like create table foo (a int, b int, c int) create table foo_loading_source1 (a int, b int, c int) create table foo_loading_source2 (a int, b int, c int) Is there a way which can be made

Re: [GENERAL] Q:Aggregrating Weekly Production Data. How do you do it?

Ow Mun Heng wrote: Data which runs in the vicinity of a few million a week. What are the methods which will effectively provide the min/max/average/count/stdev of the weekly sample size based on different products/software mix etc. and still be able to answer correctly, what's the average

Re: [GENERAL] SELECT ... FOR UPDATE performance costs? alternatives?

Erik Jones wrote: On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote: ...to ensure that only one server is processing the queue item, so inside PGSQL, use SELECT ... FOR UPDATE to lock the row... When my server is under severe load, however, this function begins to take a long time to

Re: [GENERAL] Geographic data sources, queries and questions

I don't believe this is good design. You'll have to have a trigger or something to verify that the country_id+state_id on the city table are exactly equal to the country_id+state_id on the state table. If you don't, you might have something like (using US city names...) country: USA

Re: [GENERAL] Integrity on large sites

- Original Message Follows - From: Stuart Cooper [EMAIL PROTECTED] *Really* big sites don't ever have referential integrity. Or if the few spots they do (like with financial transactions) it's implemented on the application level (via, say, optimistic locking), never the database

Re: [GENERAL] Is there a shortage of postgresql skilled ops people

Vivek Khera wrote: On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote: Another thing is this, how hard could it possibly be for a MS SQL DBA or Oracle DBA to pick up using PostgreSQL? I don't think it would take a decent admin of any database to come up to speed in a very short time as long as

Re: [GENERAL] Curious situation - running program cannot seem to delete records.

Andrew Edson wrote: I've been given a file to maintain, the purpose of which is to purge the database of records more than two years old. (Database setup is pg 8.1.3) The program (written in perl) enters postgres as the user 'postgres', and is supposed to select foreign-key records from

Re: [GENERAL] SQL Newbie Question

Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/25/07 09:30, Inoqulath wrote: Hello Folks Have a look at this Table: CREATE TABLE foo( id serial, a_name text, CONSTRAINT un_name UNIQUE (a_name)); Obviously, inserting a string twice results in an error ...is there

Re: [GENERAL] Backup the part of postgres database

Joshua D. Drake wrote: roopa perumalraja wrote: Thanks a lot for your immediate reply. can you please tell me how to use the command pg_dump to backup specific tables. Thanks a lot in advance. pg_dump --help Better yet, try this first: http://www.catb.org/~esr/faqs/smart-questions.html;.

Re: [GENERAL] Combining data in different rows?

Bruno Wolff III wrote: On Tue, Jan 02, 2007 at 12:33:14 -0700, Anthony Masinton [EMAIL PROTECTED] wrote: I would like to combine data from different rows in one column into one row. ... Is this possible and if so, how? Collapsing rows is done with aggregate functions. You

Re: [GENERAL] SUBSTRING for a regular expression

SELECT SUBSTRING(description FROM '(.*), \\d{1,3},') AS vname, SUBSTRING(description FROM '.*, (\\d{1,3}),') AS age, SUBSTRING(description FROM '\\d{1,3}, of (.*?),? was charged') AS address, SUBSTRING(description FROM ' was charged ([^ ]+)') AS dow, SUBSTRING(description FROM '

[GENERAL] SUBSTRING for a regular expression

I can't seem to get right the regular expression for parsing data like these four sample rows (names and addresses changed to ficticious values) from a text-type column: Yolanda Harris, 38, of 40 South Main St., Newtown City, was charged Sunday with breach of peace and interfering with a police

Re: [GENERAL] Securing a db app - RFC

On Wednesday 02 June 2004 02:04, BARTKO Zoltan wrote: I would appreciate anyone wiser than me to comment on the following: I am making an app for PostgreSQL (the server). The clients are connecting through the same single user. ... If I want to access a function (like do this or that with

Re: [GENERAL] Naive schema questions

On 5/27/2004 7:15 AM, Rory Campbell-Lange wrote: seems to suggest that the functions are schema specific. It is even better. The property that set's your schema context is called search_path. This contains a list of schema names. For an unqualified (schema name not explicitly given)

Re: [GENERAL] Data Encryption in PostgreSQL, and a Tutorial.

Dias Bantekas wrote: does any one know how to get an md5()-like hash function using pgcrypto for postgresql 7.3 ? without upgrading to 7.4 Thanks for any input. SELECT encode(digest(v_password, 'md5'), 'hex'); BTW, /usr/share/pgsql/contrib/pgcrypto.sql is the script that defines the

Re: [GENERAL] db schema diff

Suppose I have pg_dump -s of two pg installs, one is dev, another is production. Their schemas don't differ too much, and I want to get a diff -u-like schema diff so I can quickly add missing/remove old tables/sequences/etc to one or another (manually). Is there some quick tool for doing

Re: [GENERAL] execute function after user connect

On Fri, Apr 02, 2004 at 11:58:42 -0500, John DeSoi [EMAIL PROTECTED] wrote: Is there a mechanism to execute a function after the user connects to the database? Something like an AFTER CONNECT trigger (might as well as for a BEFORE DISCONNECT trigger, too!). I've inquired about this

Re: [GENERAL] Simplyfying many equals in a join

Is there a shorthand notation when performing a multi-table join and What's the difference between a multi-table join and a join? one column is to be equaled in all tables? Is this you are looking for? SELECT t1.c7,t2.c6 FROM t1,t2 USING (c1,c2,c3) WHERE t1.c4='2004-2-28' AND

Re: [GENERAL] plpgsql string concatanation

One thing that I always have to remind myself of is this: string || NULL is NULL. It is usually a good idea to use coalesce() unless you want this to happen. See my recent post at http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=24725 (and in general, everyone

Re: [GENERAL] Conservation of OIDs

Whoa! You mean these aren't already separate database clusters or even separate systems? I am very shocked, you can't do a proper Dev -- QAT -- Prod environment if all three systems are run by the same postmaster, or on the same host imo. I can see having separate clusters would save me the

Re: [GENERAL] Conservation of OIDs

If you're really concerned, you can initdb separate clusters for QAT and DEV and run three postmasters using three different ports. Follow-up question: Are different ports really necessary? I currently have the three different databases defined all in the same cluster, and differentiated by

Re: [GENERAL] Conservation of OIDs

On Fri, 14 Nov 2003 10:01:51 -0500 (EST), [EMAIL PROTECTED] wrote: The Production database is the real data, and we periodically take a back up from Prod and re-instantiate QAT and DEV by dropping them and then restoring from the Prod backup. Not that OID's are in short supply, but I'm anal

Re: [GENERAL] on connect trigger?

Quoting [EMAIL PROTECTED]: Is there any kind of mechanism in pg 7.3 for doing something like what I would describe as a login trigger procedure to run when a user connects to the database? Berend, I've got something like that setup on an e-communities site I built. There was already a

[GENERAL] on connect trigger?

Is there any kind of mechanism in pg 7.3 for doing something like what I would describe as a login trigger procedure or maybe on connect trigger, i.e., a way to specify a stored procedure to run when a user connects to the database? What I'm thinking is this. Right now, my end-user GUI

[GENERAL] on connect trigger

Is there any kind of mechanism in pg 7.3 for doing something like what I would describe as a login trigger procedure or maybe on connect trigger, i.e., a way to specify a stored procedure to run when a user connects to the database? What I'm thinking is this. Right now, my end-user GUI

Re: [GENERAL] Sequences without blank holes

On Thu, Nov 06, 2003 at 05:01:54 -0300, MaRcElO PeReIrA [EMAIL PROTECTED] wrote: $ select * from products; prod_id | description +- 1 | S470DXBLM 12 | S470DXABM 33 | RG250DX +- (3 rows) and it is ok to me,

[GENERAL] Temporary tables and miscellaneous schemas

Whenever I create a temporary table, with something like CREATE TEMPORARY TABLE temptable1 AS SELECT * FROM paid.ad_hoc_query; New schemas appear, with names like pg_temp_1. I guess the appearance of these schemas with temp in the name indicates that they are temporary schemas and related to the

Re: [GENERAL] Generating a SQL Server population routine

In article [EMAIL PROTECTED], Mike Mascari [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: Has some one come up with a similar type script that could be used in a Postgresql database? The script below was created for a SQLServer database. Thx, -Martin I haven't. But I was wondering

[GENERAL] Adding missing FROM-clause entry in subquery

I run the following script to export some data from my development database and then update or insert the records into to the quality assurance testing database, but I get a warning notice that I don't understand. Aside from that notice, the script appears to work as intended, i.e., updating

Re: [GENERAL] Where are user-defined types stored/viewed

On Monday 29 September 2003 13:35, [EMAIL PROTECTED] wrote: After I execute a command like CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer, employee_pk integer, hourly_dollars double precision, annual_dollars double precision); where does this

[GENERAL] Where are user-defined types stored/viewed

After I execute a command like CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer, employee_pk integer, hourly_dollars double precision, annual_dollars double precision); where does this definition get stored, and what query can I run to get teh definition back

Re: [GENERAL] career in SQL/Database administration

I think most careers these days are shifting towards package jobs where one guy does everything. Either that, or you work for a consulting/contractor outfit, and the customer periodically cancels the contract in order to play hardball and negotiate for lower rates. Then you find yourself out

[GENERAL] Column defaults fail with rules on view

I'm finding that column defaults are not being assigned to nulls when I do an insert by way of a an ON INSERT rule on a view. For example, the following script \set ON_ERROR_STOP ON \c template1 --DROP DATABASE testdb; CREATE DATABASE testdb; \c testdb create table test_table ( field1 char(1)

Re: [GENERAL] Column defaults fail with rules on view

On Friday 19 September 2003 09:00, [EMAIL PROTECTED] wrote: I'm finding that column defaults are not being assigned to nulls when I do an insert by way of a an ON INSERT rule on a view. For example, the following script [snip] Hmm - well, you're explicitly telling it to insert VALUES (...,

Re: [GENERAL] Picture with Postgres and Delphi

If You are using Delphi, there is great project called Zeos objects, and if I remember correctly it has support for large objects. Zeos are useing a lot of memory... Another issue I had with Zeos was that when I looked into possibly using those components (this was probably over a year

Re: [GENERAL] pg_dump and alter database

[EMAIL PROTECTED] writes: The problem I have is that the SQL DDL and DML produced by pg_dump fails to include the ALTER DATABASE ... SET search_path ... statement that sets the search path for when I re-load the database from a dump file. This functionality is in pg_dumpall, not pg_dump.

Re: [GENERAL] pg_dump and alter database

[EMAIL PROTECTED] writes: I HAVE used pg_dumpall -g to make a backup of of users and groups, but this output does not include the ALTER DATABASE commands. No, it looks like pg_dumpall dumps ALTER DATABASE operations for a particular database when it dumps that database. Does the ALTER

Re: [GENERAL] how to drop table named user

We accidentally created a table called user in our public schema. We are now trying to remove the table using DROP TABLE user and even DROP TABLE public.user. However every time we try we get an error message: ERROR: parser: parse error at or near user at character 12 Any suggestion on

Re: [GENERAL] Simulating sequences

wouldn't a better situation be ADDING a record that is one higher, and then doing a select MAX()? The different triggers could do delete on the old records. In my case that would not apply, because what I had was a need to keep a sequence counter for each employee, so I added a column

Re: [GENERAL] How many fields in a table are too many

On Thu, Jun 26, 2003 at 03:17:12AM -0400, [EMAIL PROTECTED] wrote: I have a table with 13 fields. Is that too many fields for one table. Mark Thirteen? No way. I've got you beat with 21: Pfft! Is *that* all? I've got a table with 116 fields. I *knew* a number of these responses

Re: [GENERAL] Schemas and access

I am just beginning to investigate schemas, and have run into a problem. Searching the mailing lists and documentation doesn't help. This is 7.3.3 on Linux, kernel 2.4.21, good ole Slackware, compiled from source. Two users, JoeBob and MaryJo. JoeBob has schema xyzzy with table plugh.