[GENERAL] Will a DELETE violate an FK?

Is there anyway to know if a DELETE will violate an FK without actually trying it?

Re: [GENERAL] Adding auto-increment / sequence

am Mon, dem 21.05.2007, um 18:29:46 -0700 mailte camb folgendes: > Hey all, > Is there any way to add some kind of sequence of auto-incrementing > column to the result set returned by a SELECT? Yes. You can use an sequence similar this: test=# select * from foo; col1 | col2 | col3 --+

Re: [GENERAL] Call a program

am Tue, dem 22.05.2007, um 16:19:55 -0400 mailte Pablo Hume folgendes: > Hi, sorry for my english, i?m from Paraguay. > > I need to know if exists a way to call a program like CMD (command prompt) > from > a procedure or a trigger, and execute an external application. Yes, this is possible. You

Re: [GENERAL] Stored Procedures Performance: Java vs. PL/Pgsql

[EMAIL PROTECTED] (Daniel Kastenholz) writes: > Hi, > > is there a rule of thumb or an estimate which language provides the > best performance for stored procedures? > > I'm currently using PL/Pgsql, and wondering > - to what extent these stored procedures are actually precompiled > and/or optimize

Re: [GENERAL] Uhm, so, yeah, speaking of /.

On Thu, 2007-05-24 at 19:15 -0600, Scott Ribe wrote: > Just trying to post, and getting this error message: > > > Invalid form key: c3pVklZBr9 > > > > Chances are, you're behind a firewall or proxy, or clicked the Back button > > to > > accidentally reuse a form. Please try again. If the problem

Re: [GENERAL] SQL Manager 2007 for PostgreSQL released

On May 22, 2007, at 19:47 , EMS Database Management Solutions (SQLManager.net) wrote: We, here at EMS Database Management Solutions, are pleased to announce SQL Manager 2007 for PostgreSQL - the new major version of the powerful PostgreSQL administration and development tool! Congratulatio

[GENERAL] arrays in where

I have a table select * from history; idx | tokens -+- 2 | {10633,10634,10636} And the values in the tokens field are taken from sequence values from another table. Can I use this kind of storage to identify all the tokens in the first table that make

Re: [GENERAL] Possible DB corruption

On Mon, May 28, 2007 at 04:14:14PM -0600, Michael Fuhr wrote: > On Fri, May 25, 2007 at 04:47:52PM -0400, Justin M Wozniak wrote: > > We noticed that some records were mysteriously disappearing from > > our DB. I went in with psql and found that the \dt command no longer > > works, providing t

Re: [GENERAL] Possible DB corruption

On Fri, May 25, 2007 at 04:47:52PM -0400, Justin M Wozniak wrote: > We noticed that some records were mysteriously disappearing from > our DB. I went in with psql and found that the \dt command no longer > works, providing the output below. Is this a sure sign of DB corruption? > Running ps

Re: [GENERAL] Possible DB corruption

Justin M Wozniak <[EMAIL PROTECTED]> writes: > We noticed that some records were mysteriously disappearing from > our DB. I went in with psql and found that the \dt command no longer > works, providing the output below. Is this a sure sign of DB corruption? > Running psql (PostgreSQL) 7.4.8

Re: [GENERAL] SQL Manager 2007 for PostgreSQL released

On 22 May 2007 17:47:46 -0700, EMS Database Management Solutions (SQLManager.net) <[EMAIL PROTECTED]> wrote: We, here at EMS Database Management Solutions, are pleased to announce SQL Manager 2007 for PostgreSQL - the new major version of the powerful PostgreSQL administration and development too

Re: [GENERAL] help with query...

marcelo Cortez <[EMAIL PROTECTED]> writes: > I am confused , > way planer it does not use the partial index? > query: > SELECT cliente_base.* FROM cliente_base > WHERE (cliente_base.inst_class_ = 'Cliente' ) ORDER > BY cliente_base.nombre ASC Because it thinks the seqscan is cheaper.

Re: [GENERAL] SQL Manager 2007 for PostgreSQL released

On 23/05/2007 11:40, L. Berger wrote: Thanks for this, but is there any plan to launch something like this for use on Linux admin servers? Something that I could install on a server, and perhaps work with a web interface? I would love some recommendations. Have you tried PHPPgAdmin? I use it a

Re: [GENERAL] CUBE SYNTAX

On Sun, May 27, 2007 at 01:23:48PM -0400, ABHANG RANE wrote: > Hi, > I need to use the cube operator in postgresql 8.2. Whats the syntax > for the cube or a link to the documentation which clearly explains > how to use cube operator syntactically. There is a contrib module for cube.

Re: [GENERAL] How to implement GOMONTH function

"Andrus" <[EMAIL PROTECTED]> writes: > Why numeric(2) is not casted to integer automatically ? Because it would lose data, eg '4.4' being rounded to 4. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is you

[GENERAL] postgres access - pg_hba.conf

Hi.. I'm trying to setup an app (gforge) to communicate using the postgres db. I've modified the pg_hba.conf file and I can sort of get access. However, it appears that I can't access the database using a passwd. I assume I'm using the wrong passwd, although I created the db, and thought that thi

[GENERAL] can i recover the database to a certain point...

hello. i deleted and recreated a db with the same name. is it possible to recover the database back to where it was before i deleted it? i'd like to find this out first before i try to do some recovery process. thanks a lot. ---(end of broadcast)-

[GENERAL] Rookie Questions: Storing the results of calculations vs. not?

PG: I am playing around with some historical stock option data, and have decided to use a database to make my life easier. The data is "end-of- day" for all equitable options, so something like 17 columns and approximately 200,000 rows a day. I have several months of data in "csv" format, one fi

Re: [GENERAL] Limiting number of rows returned at a time in select query

On Thu, May 24, 2007 at 05:32:25AM -0700, Jon Clements wrote: > have a .NET driver that has a FetchSize option which allows the above > (say returning 10k rows at a time without holding them all in memory); > I'm not sure though if that's a property of the driver / server, as > none of the other in

[GENERAL] ERROR: cache lookup failed for type 0

Hi, Am not sure if this is something we've done wrong or maybe a bug. Whenever any kind of query is done on the table below, this is the result: ispdb_vxe=> select * from pm.carrier_on_13642; ERROR: cache lookup failed for type 0 I first noticed it when I noticed that the regular backups were

[GENERAL] Possible DB corruption

Hello We noticed that some records were mysteriously disappearing from our DB. I went in with psql and found that the \dt command no longer works, providing the output below. Is this a sure sign of DB corruption? Running psql (PostgreSQL) 7.4.8 on Linux 2.4.27. Thanks GEMSd=> \dt

Re: [GENERAL] Delete with subquery deleting all records

Your top-posting was confusing to me, but I eventually figured out what went where. Francisco Reyes wrote: However.. I find it very strange that just the selects by themselves produced the same ouput up to limit 100. Strange? Why? Did you expect a particular statistical distribution? Perha

[GENERAL] hundreds of schema vs hundreds of databases

Hi group, I have an application with some hundreds users, each one having the same data definitions, and each one storing up to 2 GB of data. A user have just access to his own data. His data will have its own tablespace. Therefore, it seems to me I have a choice between "one database per us

[GENERAL] How to create trigger if it does not exist

CREATE TRIGGER mycheck_trigger BEFORE INSERT OR UPDATE ON mytbl FOR EACH ROW EXECUTE PROCEDURE mycheck_pkey(); aborts transaction if trigger already exists. There in no CREATE OR REPLACE TRIGGER command in PostgreSQL How to create trigger only when it does not exist ? Andrus. --

Re: [GENERAL] the future of pljava development

I used to do Oracle development and could choose between PLSQL and the java alternative. I chose PLSQL because java offerred no clear advantage, and it wasn't really java. You could use String, but for performance Oracle recommended some proprietary string class. If I'm remembering correctly, y

Re: [GENERAL] How to create trigger if it does not exist

>> How to create trigger only when it does not exist ? > DROP TRIGGER IF EXISTS... > > See: > > http://www.postgresql.org/docs/8.2/static/sql-droptrigger.html Thank you. This doc says that dropping trigger drops depending objects also. Which objects depend on the user-defined trigger so that the

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

You can try the free sample database from http://www.geodatasource.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

[GENERAL] Size of large object

Hi I have a serlized data and i could not find the total size of data.maybe postgresql can help me .how can i calculate it. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

[GENERAL] Call a program

Hi, sorry for my english, i’m from Paraguay. I need to know if exists a way to call a program like CMD (command prompt) from a procedure or a trigger, and execute an external application. Thanks. En español: Hola, me gustaria saber si existe alguna manera de ejecutar desde un procedimi

Re: [GENERAL] How to create trigger if it does not exist

"Rodrigo De León" <[EMAIL PROTECTED]> kirjutas sõnumis news:[EMAIL PROTECTED] > On May 26, 5:58 pm, "Andrus" <[EMAIL PROTECTED]> wrote: >> Thank you. >> This doc says that dropping trigger drops depending objects also. > > Only if you use CASCADE (default is RESTRICT). > If I do not use CASCADE

[GENERAL] Monitoring Tool

Hi, time ago i have writen a Monitoring Tool in Java to perform Monitoring to a general Data Base. But i have writen to monitoring a postgresql. The idea is that you connect to the database and do some querys to the system tables, asking for some values. All theses things are configurable in a XML

Re: [GENERAL] How to implement GOMONTH function

Alban, Why would you want to call the function with a numeric? What does 1.2 months mean to you? You're probably only interested in the integer part of the numeric. create table test ( m numeric(2) ); select gomonth( current_date, m ) from test; ERROR: function gomonth(date, numeric) does not

[GENERAL] Stored Procedures Performance: Java vs. PL/Pgsql

Hi, is there a rule of thumb or an estimate which language provides the best performance for stored procedures? I'm currently using PL/Pgsql, and wondering - to what extent these stored procedures are actually precompiled and/or optimized - if Java might provide better performance, or if para

[GENERAL] Adding auto-increment / sequence

Hey all, Is there any way to add some kind of sequence of auto-incrementing column to the result set returned by a SELECT? I need to get an auto incrementing column of some kind into the result set from a view? Thanks in advance, Cam ---(end of broadcast)---

[GENERAL] dump/restore and statistics

Dear PostgreSQL deverlopers ! Why PostgreSQL do not export statistics equally with common data? So, during pg_restore it'll feed database with the right statistics... Users required to make time-consuming analyze on a whole database right after restore of the database. Another way to skip this u

[GENERAL]

so 2 follow-up questions: 1. Does this mean that I have to examine programmatically every token that I pass to tsearch2, substituting these placeholders for 'c#' and 'c++'? Seems like brute force to me.. 2. Can I configure tsearch2 to do this for me? If so, how? I think I'm looking for the sop

[GENERAL] Best way to prevent overlapping dates

I need to disable rows with overlapping dates in 8.1+ I created the following trigger procedure for this. Is this best way ? Will it prevent overlapping rows in all cases ? Andrus. create table puhkus (reanr serial primary key, palgus date, plopp date); CREATE OR REPLACE FUNCTION puhkus_seq

[GENERAL] Inheritance question

First, I want to confess that I am not an SQL expert or even remotely close. :-) Second, I believe I pretty much know the answer to my question, but I would like to have some confirmation if you fine people don't mind. My situation is this: I have a PHP script that some what dynamically genera

[GENERAL] help with query

Folks I am confused , way planer it does not use the partial index? any help be appreciated pd: sorry for my english. data: table definition: -- Table: cliente_base CREATE TABLE cliente_base ( id_ integer NOT NULL, activo boolean, numero integer, categ character vary

Re: [GENERAL] Integrity on large sites

PFC wrote: "*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 level." Sure, but in the forum benchmark I just did, when using

[GENERAL] help with query...

Folks I am confused , way planer it does not use the partial index? query: SELECT cliente_base.* FROM cliente_base WHERE (cliente_base.inst_class_ = 'Cliente' ) ORDER BY cliente_base.nombre ASC plan: QUERY PLAN

Re: [GENERAL] doverlaps() returns null

You cannot cast +/- infinity timestamp to date Thank you. All my dates are in nearest centuries. So I fixed this by creating function CREATE OR REPLACE FUNCTION public.doverlaps(date,date, date, date, out bool) immutable AS $_$ SELECT coalesce($1, date '0001-01-01') <=coalesce($4, date '999

Re: [GENERAL] SQL Manager 2007 for PostgreSQL released

On May 23, 8:47 am, "EMS Database Management Solutions (SQLManager.net)" <[EMAIL PROTECTED]> wrote: > We, here at EMS Database Management Solutions, are pleased to announce > SQL Manager 2007 for PostgreSQL - the new major version of the > powerful PostgreSQL administration and development tool! >

Re: [GENERAL] FULL JOIN is only supported with merge-joinable join conditions

I think the way to get the result you want is to suppress the null-containing rows before they get to the FULL JOIN, like so: regression=# SELECT * FROM (SELECT * FROM iandmed WHERE ametikoht IS NOT NULL) AS iandmed FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht; Thank you. In my

[GENERAL] SQL Manager 2007 for PostgreSQL released

We, here at EMS Database Management Solutions, are pleased to announce SQL Manager 2007 for PostgreSQL - the new major version of the powerful PostgreSQL administration and development tool! You can download SQL Manager 2007 for PostgreSQL at: http://www.sqlmanager.net/products/postgresql/manager/

[GENERAL] application online upgrade strategy

Does anyone have an idea on where to turn for information/research papers on online application upgrades? I am well-versed in offline upgrades, but I am now faced with customer requirements for transparent software upgrades of our application server/services -- and what possible solutions are the

[GENERAL] server side LO

I'm writing the server side function what process large object, and i need the file descriptor for this LO. I looked in be-fsstubs.c, but i'm confused. Any simpler examples? I think i can create procedure in plpgsql like this: fd := lo_open(oid); result := my_func(fd); lo_close(fd); It's will

[GENERAL] Limiting number of rows returned at a time in select query

Hi All. Is there a way inside a query (or connection) to limit the amount of records returned each chunk by the server? At the moment, I have 22 million records trying to be returned in one-go as the result set. I have a .NET driver that has a FetchSize option which allows the above (say returning

[GENERAL] Problem with pg_dump.

Hi! I've got problem with dumping the database on the slave server. Situation: Master: Running CentOS release 4.4 (Final) with PostgreSQL 8.2.4. Slave: Running CentOS release 4.3 (Final) with PostgreSQL 8.2.4 and slon version 1.2.9 pg_dump on master works fine. On slave i've got error on st

Re: [GENERAL] pg_ctl: could not start service "pgsql-8.2": error code 1063

postmaster starting Execution of PostgreSQL C:\apache2triad\pgsql\bin> by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information on how to properly

Re: [GENERAL] createdb.exe prompting for password on Vista

On Mon, May 28, 2007 at 09:13:20PM +0200, Greg Quinn wrote: > I'm thinking the best way to get around it is change the auth option to > trust in my installer, create the database, and then automatically set the > auth option back to "md5". Not the most elegant solution but it will work > for now.

Re: [GENERAL] optimisation for a table with frequently used query

SELECT * from my_table where id_1 = x and id_2 = y; Neither id_1 or id_2 or the combination of them is unique. I expect this table to become large over time. Create an index on (id_1, id_2), or (id_2,id_1). ---(end of broadcast)--- TIP 9: In v

Re: [GENERAL] createdb.exe prompting for password on Vista

Setting the authentication option to "trust" seems to work but now I'm worried my application won't need a password for users accessing the database. It is a client-server application so I don't know which IP addresses can/can't use it and I'm not relying my application to be on a windows domain/ac

[GENERAL] optimisation for a table with frequently used query

Hi, I have a table that looks like this: CREATE TABLE my_table { id SERIAL PRIMARY KEY, id_1 INTEGER REFERENCES tab1(id), id_2 INTEGER REFERENCES tab2(id), . . . }; I will often be running queries that look like SELECT * from my_table where id_1 = x and id_2 = y; Neither id_1 or id

Re: [GENERAL] Different sort order

Try on both servers: show lc_collate Unfortunately you have to initdb to set the locale. You can't set it per database or change it once it's initialized. Right you are, but I suppose I can do a dump using pg_dumpall overwrite the cluster with initdb --locale=en_US and restore it agai

Re: [GENERAL] Different sort order

What's the output of the following query on each server? select name, setting from pg_settings where name ~ '^lc_'; Ahh there's a difference mydb=# select name, setting from pg_settings where name ~ '^lc_'; name | setting -+- lc_collate | en_US lc_ctype| en_U

Re: [GENERAL] Different sort order

Poul Møller Hansen <[EMAIL PROTECTED]> writes: > I'm wondering why the sort order on these two servers behaves differently. > The only difference I can see is that one is using a 32.bits Ubuntu and the > other is on 64bits versions of Ubuntu. > But why this difference ? Try on both servers: sh

Re: [GENERAL] Different sort order

On Mon, May 28, 2007 at 07:07:41PM +0200, Poul Møller Hansen wrote: > I'm wondering why the sort order on these two servers behaves differently. What's the output of the following query on each server? select name, setting from pg_settings where name ~ '^lc_'; -- Michael Fuhr -

[Re] Re: [GENERAL] Winsock error 10035 while trying to upgrade from 8.0 to 8.2

> Cyril VELTER wrote: > > > >>> Cyril VELTER wrote: > Searching the source files, it seems the error message is generated in > port/win32/socket.c line 594. > >>> Right, but the important thing is which path down to that function is it > >>> generated in. Which is why a backtrace would

Re: [GENERAL] createdb.exe prompting for password on Vista

On Mon, May 28, 2007 at 07:18:47PM +0200, Greg Quinn wrote: > And how would I enable this? > I've tried all the created password options with no luck. You'll need to look at http://www.postgresql.org/docs/8.2/static/client-authentication.html. Sorry, I know nothing about Windows, so if there's so

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

Off-topic, but the US Virgin Islands are an "unincorporated United States insular area" ("territory"--note the lowercase t). The Dept. Of the Interrior addresses this in their FAQ: http://www.doi.gov/oia/FAQ/FAQindex.htm#4 You'll be surprised and amazed at the number of US "terms of sovere

[GENERAL] Different sort order

I'm wondering why the sort order on these two servers behaves differently. The only difference I can see is that one is using a 32.bits Ubuntu and the other is on 64bits versions of Ubuntu. But why this difference ? Thanks, Poul mydb=# select * from test order by felt1; felt1 test1

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

John D. Burger wrote: For instance, does the US have 50 states - what about the US Virgin Islands, etc? Off-topic, but the US Virgin Islands are an "unincorporated United States insular area" ("territory"--note the lowercase t). The Dept. Of the Interrior addresses this in their FAQ: http

Re: [GENERAL] createdb.exe prompting for password on Vista

And how would I enable this? I've tried all the created password options with no luck. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: 28 May 2007 02:52 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] createdb.exe prompting

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

Chuck D. wrote: I decided to put together the USGS stuff, the maxmind free stuff and the GeoNames project files and in the end I had countries with no states, states with no cities and cities with no states. Some data sources said a country had 40 states, another said it had 50. It was di

Re: [GENERAL] why postgresql over other RDBMS

On Sat, May 26, 2007 at 02:33:05PM -0400, Tom Lane wrote: > making userids be [EMAIL PROTECTED] not just a username). This is probably > why > they still haven't got IPv6 support: > http://bugs.mysql.com/bug.php?id=8836 > I wonder what their plans are for fixing that ... Panic at the last second

Re: [GENERAL] swap storm created by 8.2.3

Joseph S <[EMAIL PROTECTED]> writes: > I set the ulimit for data to 4194304k: I've noticed that ulimit -d doesn't seem to do what you might expect on Linuxen. Try -m and/or -v. regards, tom lane ---(end of broadcast)--- TIP

Re: [GENERAL] createdb.exe prompting for password on Vista

On Sun, May 27, 2007 at 09:02:56PM +0200, Greg Quinn wrote: > I am using createdb to automatically create a database from a .sql file, but > in Vista it seems to prompt me for the postgres password. > > How can I stop this from happening? I'm not using the -W or -password option > so why does it a

Re: [GENERAL] table referencing several others

Here what I would do : - Have a table "objects", which contains locations, shows, persons, well anything you want to relate to the forums in question. - Simply add an object_id to your forums table. Now, how do you distinguish between different types of objects in your ob

Re: [GENERAL] crash creating tsearch2 index

Could you provide a test suite? John DeSoi wrote: Hi, I'm trying to dump and restore a copy of a database in the same cluster. pg_restore would abort when creating a tsearch2 gist index. So I dumped to text removed the CREATE INDEX commands and tried to do that at the end after the rest of t

[GENERAL] table referencing several others

Hello, To support a forum application I have a "forum" table: Column|Type -+- created_by | integer created_on | timestamp without time zone modified_by | integer modified_on |