> I am however unable to do the same successfully (the Java code simply
> hangs, probably as a result of the second psql not getting the input
to
> it) from Java code using objects of ProcessBuilder and Process. I have
> used threads consume the STDOUT and STDERR streams (I write the STDOUT
> strea
>
> On Mon, Sep 15, 2008 at 2:04 PM, Christophe <[EMAIL PROTECTED]> wrote:
> >
> > On Sep 15, 2008, at 12:56 PM, Scott Marlowe wrote:
> >>
> >> I could totally get behind needing permission to see the plpgsql
code.
> >
> > :)
>
> I wasn't kidding up there. Setting view permissions on plpgsql (or
>
> Oh, as I was writing a CUBE query today and realized that I forgot to
> mention this. And unlike most gripes, like MERGE INTO or CTE's which
are
> really convenience things, this is key piece of functionality that
you
> just can't reproduce in Postgres.
>
> That said, there's not the same s
PostgreSQL has table partitioning in it so you don't have to dynamically
figure out which table to get the data from.
http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html
However, you can achieve dynamic SQL in plpgsql too.
http://www.postgresql.org/docs/8.3/interactive/ecpg-dy
Sounds like you really want this:
create table customer (
id serial primary key,
name text
);
create table location (
id serial primary key,
name text,
customer_id int references customer (id) );
create table product (
id serial primary key,
name text,
location_id
> On Fri, Aug 29, 2008 at 1:40 PM, Roberts, Jon
<[EMAIL PROTECTED]>
> wrote:
>
> >> Why have you got thousands of them? If you are running with
thousands
> >> of active backends, may I suggest a connection pooler?
> >>
> >
> > I don&#x
> -Original Message-
>
> "Roberts, Jon" <[EMAIL PROTECTED]> writes:
> > I am noticing a large number of temp schemas in my database. We use
> > temp tables but it doesn't appear that the schemas get dropped for
some
> > reason.
>
>
I am noticing a large number of temp schemas in my database. We use
temp tables but it doesn't appear that the schemas get dropped for some
reason. This greatly slows down how long it takes pgAdmin to connect
because it retrieves thousands of pg_temp_% schemas.
We mainly use "drop on commit" and
You can using the pg_hba.conf file. Set the non-local accounts to
reject when you start the database. After you finish your scripts,
change the pg_hba.conf file to enable logins and then use pg_ctl reload
to enable the new pg_hba.conf file.
host all all 0.0.0.0/0 reject
And then change it
> What I am trying to do is find the difference between two tables, one
> that stores the
> information in a single column, and the other which stores the same
data
> in multiple
> columns.
>
> E.g.
> CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
text,
> col6 text, col7 text,
> > I can find very little information on hot updates but I found this:
> http://archives.postgresql.org/pgsql-patches/2006-11/msg00059.php
> >
> > It states, "This design optimizies the updates when none of the
index
> columns are modified and length of the tuple remains the same after
> update."
> Roberts, Jon escribió:
>
> > Why would you set the fillfactor to anything other than 100 for a
> > PostgreSQL table?
>
> To favor HOT updates.
>
> --
I can find very little information on hot updates but I found this:
http://archives.postgresql.org/pgsql-pa
> >
> >> Is there any way to set a different default fill factor?
> >
> > ALTER TABLE Yourtable
> >SET ( FILLFACTOR = 50 );
>
> Hehe. I know how to do that. I mean the default fill factor for a
> database / user for tables / indexes.. Like setting search_path for a
> user.
>
> alter dat
> Roberts, Jon wrote:
> >> Not having looked at the internals of db_link, I'd say it's
certainly
> >> possible that this is the reason for the failed restart. If db_link
is
> >> blocking something, the postmaster can't kill it off, and it'll
stil
>
> Not having looked at the internals of db_link, I'd say it's certainly
> possible that this is the reason for the failed restart. If db_link is
> blocking something, the postmaster can't kill it off, and it'll still
be
> sitting there holding a reference to the shared memory segment.
>
> That
> Roberts, Jon wrote:
> >> Tom Lane wrote:
> >>> "Roberts, Jon" <[EMAIL PROTECTED]> writes:
> >>>> Version: "PostgreSQL 8.3.0, compiled by Visual C++ build 1400"
> >>> Well, there are plenty of known bugs in 8.3.0 by now.
> Magnus Hagander <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> But the syslogger process (and maybe others) is *not* supposed to
die.
>
> > Right. But are you saying we actually want to start up a new backend
in
> > a directory where we already have a running syslogger (and maybe
others)
>
> Tom Lane wrote:
> > "Roberts, Jon" <[EMAIL PROTECTED]> writes:
> >> Version: "PostgreSQL 8.3.0, compiled by Visual C++ build 1400"
> >
> > Well, there are plenty of known bugs in 8.3.0 by now. You really
> > should update before
> "Roberts, Jon" <[EMAIL PROTECTED]> writes:
> > Version: "PostgreSQL 8.3.0, compiled by Visual C++ build 1400"
>
> Well, there are plenty of known bugs in 8.3.0 by now. You really
> should update before complaining, not after.
I'm not complai
http://www.sqlmaestro.com/products/postgresql/
I've used the PHP Code Generator with great success for simple stuff
like you describe. You could then write a function to do email
notifications or whatever you want.
Jon
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-genera
Platform: Windows 2003 Server 32 bit
Windows SharedSection set to 1024,3072 based on recommendations from
http://www.postgresql.org/docs/faqs.FAQ_windows.html#4.4 and
http://support.microsoft.com/kb/184802
Version: "PostgreSQL 8.3.0, compiled by Visual C++ build 1400"
Autovacuum: on
Virus Scanning:
Character will use more disk space than varchar so it does make a
difference.
http://www.postgresql.org/docs/8.3/interactive/datatype-character.html
Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
paddi
I need a high performing version of Oracle's connect by functionality in
PostgreSQL. I saw some dispute about attempts to add this in the
archives and a reference to an ANSI alternative "with" statement. Is
either of these functions available yet? I'm using 8.3.
Jon
--
Sent via pgsql-general
> On Fri, Jun 6, 2008 at 9:36 AM, Roberts, Jon <[EMAIL PROTECTED]>
> wrote:
> > In Oracle, there is a method to determine when it is advisable to
> > rebuild indexes. Are there any guidelines for this in PostgreSQL?
> >
> > I found this but it doesn't i
In Oracle, there is a method to determine when it is advisable to
rebuild indexes. Are there any guidelines for this in PostgreSQL?
I found this but it doesn't indicate at which point an index should be
rebuilt other than corruption.
http://www.postgresql.org/docs/8.3/interactive/routine-reindex
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Guy Rouillier
> Sent: Wednesday, June 04, 2008 1:33 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] does postgresql works on distributed systems?
>
> Justin wrote:
> >
> >
> > aravind chandu wrote:
> > Hi,
> > >> My question is
> > >>Microsoft sql server 2005 cannot be shared on multiple systems
> > i,e in a network environment when it is installed in one system it
> > cannot be accessed one other systems.
> >
> >
> > This don't make any
You can do this with dblink
http://www.postgresql.org/docs/8.3/interactive/contrib-dblink.html
pretty easily.
Jon
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Pedro Doria Meunier
> Sent: Sunday, May 25, 2008 6:25 AM
> To: Postgres
I'm assuming you are having problems because the tree structure allows
for n levels and you need to get all of the child records.
I am guessing you mean this:
create table cms_items
(itm_id integer primary key not null,
itm_parent integer default 0 not null,
url varchar(100) not null);
insert
You have commcost in the correlated subquery which shouldn't be there.
Here are three ways to achieve the results you want:
select commcost.maplot,
commcost.unitno
from commcost
where not exists(select null
from bldg
where commcost.maplot = bldg.
Thanks for posting this. I had been using DB Designer 4 which has tons
of bugs in it. Power Architect is pretty nice.
Jon
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Thomas Kellerer
> Sent: Wednesday, May 21, 2008 2:01 AM
> To:
Greenplum has it. Maybe they are planning on adding it to Bizgres or
PostgreSQL.
Jon
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Hannes Dorbath
> Sent: Sunday, May 04, 2008 8:02 AM
> To: pgsql-general@postgresql.org
> Subject: [
> -Original Message-
> From: Viktor Rosenfeld [mailto:[EMAIL PROTECTED]
> Sent: Monday, April 28, 2008 4:52 PM
> To: Roberts, Jon
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] passing a temporary table with more than one
column
> to a stored procedure
> is there a way to pass a temporary result table with more than column
> to a stored procedure?
>
> Specifically, I would like to run a variation of this query:
>
> SELECT
> *
> FROM
> graphOverTokens(
> (
> SELECT 1 AS min, 10 AS max, 2 AS text_re
I had a problem with a database yesterday on a Windows server. The
service was described as executing "C:\Program
Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D
"E:\PostgreSQL\data\". I also had an old backup of the data directory
from 8.2. It was located on E:\PostgreSQL\d
> On 23/04/2008 20:33, Roberts, Jon wrote:
>
> > create table ugly
>
> [...snip...]
>
> > create or replace function fn_ugly() returns setof ugly as
>
> [...snip...]
>
> > create temporary table temp_ugly
>
> [...snip...]
>
> > se
You really don't have duplicate data and you should redesign your table
structure. However, here is a way to do it.
create table ugly
(aid integer, bid integer);
insert into ugly (aid, bid) values (1,5);
insert into ugly (aid, bid) values (2,6);
insert into ugly (aid, bid) values (3,7);
insert
I'm looking for a good BI tool to query data in PostgreSQL. I'm not
looking for a "pixel perfect" reporting tool but a tool that supports
things like:
* Star Schema
* Drill up/down with hierarchies defined in metadata
* Self service
* Graphing
* Scheduling
In other words, I want a ROLAP visualiz
> Terry Lee Tucker wrote:
> > On Wednesday 23 April 2008 06:46, Pascal Cohen wrote:
> >
> >> Hello
> >> I am playing with security in Postgres
> >> And I would like to have a database that can be managed by a given
user
> >> that could do almost anything but I would also have a user that can
> jus
t;I'm trying to determine y by looking at x".
It looks like you are trying to use a temp table when an inline view would be
more appropriate.
Jon
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kerri Reno
Sent: Tuesday, April 22,
> > Back to PostgreSQL, when a vacuum is issued, the old deleted rows
are
> > removed. This can be done with the autovacuum feature or manually.
> >
> > -Does vacuum make sure there are no long running queries referencing
the
> > deleted rows before it attempts to remove the old rows?
>
> It does
Can you explain what you mean by the "restriction to do SELECT INTO"?
Why are you using a temp table to begin with?
Jon
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kerri Reno
Sent: Tuesday, April 22, 2008 7:55 AM
To: pgsql-gen
> Pavan Deolasee, 22.04.2008 12:57:
> > On Tue, Apr 22, 2008 at 4:11 PM, Thomas Kellerer
<[EMAIL PROTECTED]>
> wrote:
> >>
> >> I am interested in the technical details on how PG determines that
a
> block
> >> needs to be read from from "some other place than the data block"
> because
> >> another
> I have an existing table in an app, along the lines of:
> >>
> >> CREATE TABLE foo (
> >> name text,
> >> address text,
> >> some_numeric_info integer,
> >>
> >> );
> >>
>
>
> I essentially need another table
>
> CREATE TABLE foo_printing_options (
> field_name text,
> print_order int
You can have a two column foreign key.
create table employee
(id int primary key not null,
company_id int not null,
supervisor_id int);
alter table employee add unique (id, company_id);
alter table employee add foreign key (supervisor_id, company_id)
references employee (id, company_id);
Jon
> > I am moving from Windows to Solaris and I need pgcrypto and dblink.
> > Where are these? I don't see anything in the configure that
suggests
> it
> > is even an option.
>
> They're not handled by 'configure'. They are in the 'contrib'
> directory in the source tree, and you install them by
I am moving from Windows to Solaris and I need pgcrypto and dblink.
Where are these? I don't see anything in the configure that suggests it
is even an option.
Jon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.o
I am migrating an 8.3 database from Windows to Solaris. We are using
pl/java and I went through the installation process for this on Windows.
I'm building Solaris from the source and when running ./configure, I
don't see a switch to include pl/java. Java is in my path too so it
should allow me
pgAgent works well for me and it is built into pgAdmin.
http://www.pgadmin.org/docs/1.8/pgagent.html
Jon
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Tim Uckun
> Sent: Sunday, April 06, 2008 11:10 PM
> To: pgsql-general@postgresq
> Hi,
>
> Le Tuesday 01 April 2008 22:00:11 Irina Connelly, vous avez écrit :
> > I am looking around for an ETL tool to transfer data from one Postgres
> > database to another periodically.
>
> You could simply rely on COPY for this, as data coming out of PG could be
> trusted to be able to get
I sent this just a few days ago to the list on a similar question. You
will need to move your code from the trigger to a regular function like
the one below. The function should handle the update to the table and
also to the linked database.
create table log (update_datetime timestamp);
> Also, 2PC is subject to unresolved transactions (or something like
that).
>
> > > Even in Oracle you don't have cross db queries.
> >
> > On the contrary you do. You can refer to objects in another
database by
> > [EMAIL PROTECTED], very useful to mix local and remote data
in no
> > time. DB
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
> Sent: Sunday, March 30, 2008 10:13 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] postgreSQL multithreading
>
> Hi all,
>
> I was trying to find some wa
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Sam Mason
> Sent: Wednesday, March 26, 2008 7:14 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] select any table
>
> On Tue, Mar 25, 2008 at 05:37:00PM -0400, Malinka Re
> > > On Tue, 25 Mar 2008 09:54:20 -0500
> > > "Roberts, Jon" <[EMAIL PROTECTED]> wrote:
> > >
> > > > I have some users that need "select any table" but they should
not
> > be
> > > > superusers. How can this be
OTECTED]
> Sent: Tuesday, March 25, 2008 11:42 AM
> To: Roberts, Jon
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to sort strings containing a dot?
>
> By the way, I have just inserted a duplicate. Then I have run the
select
> statement with distinct and
create table t (name varchar);
insert into t values ('co.aaa');
insert into t values ('co.abb');
insert into t values ('co.cab');
insert into t values ('com');
insert into t values ('co.ment');
insert into t values ('com.enta');
select name from t order by replace(name, '.', '');
Jon
> -Or
> On Tue, 25 Mar 2008 09:54:20 -0500
> "Roberts, Jon" <[EMAIL PROTECTED]> wrote:
>
> > I have some users that need "select any table" but they should not
be
> > superusers. How can this be done?
> >
> > I need a "grant select
I have some users that need "select any table" but they should not be
superusers. How can this be done?
I need a "grant select on to ".
Jon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-
A case statement will work just fine:
select case when sub.col1 = 'y' then true else false end as
col1_boolean, sub.col1 from (select cast('y' as varchar) as col1) sub
Jon
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Gordon
> Sen
AGE 'plpgsql' VOLATILE;
Now I can run these statements:
select * from admin.fn_get_dba_fn_roles();
and
select * from admin.fn_get_dba_fn_roles('abc', 'fn_123');
I wrote similar functions to show grants to tables.
Is there an easier way to handle this?
Jon
than it sounds.
Jon
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 10, 2008 4:08 PM
> To: Roberts, Jon
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] pg_type.relacl
>
> "Roberts, Jon" <[EMAIL PROT
I need to get a list of roles granted select on a table from a remote
database. Ideally, each grantee would be represented as VARCHAR and not
an ACLITEM.
If the remote database is on the same server, it works but fails on the
different server. It looks like the datatype aclitem[] is linked to
That is awesome. Can it be added to pga3?
Jon
-Original Message-
From: Dave Page [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 25, 2007 2:11 PM
To: Gregory Stark
Cc: Roberts, Jon; pgsql-general@postgresql.org
Subject: Re: subversion support?
> --- Original Mess
No I haven't. Thanks for the tip.
Jon
-Original Message-
From: Reg Me Please [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 25, 2007 11:25 AM
To: pgsql-general@postgresql.org
Cc: Tino Wildenhain; Roberts, Jon
Subject: Re: [GENERAL] subversion support?
Ever tried Druid?
the fact that pgAdmin is developed using source
control but the code you write with the tool doesn't have any hooks into
source control.
Jon
-Original Message-
From: Tino Wildenhain [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 25, 2007 11:03 AM
To: Roberts, Jon
Cc: pgsql-gene
: Thursday, October 25, 2007 11:16 AM
To: Roberts, Jon
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] subversion support?
Roberts, Jon wrote:
> I could use psql instead of pgAdmin then which isn't what I want.
>
> Having used Quest software SQL Navigator since 97 for Oracle and t
hould show history and let me see the differences.
In other words, take Tortoise and merge that product into pgAdmin so I have
one product instead of two.
Jon
-Original Message-
From: Brad Lhotsky [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 25, 2007 9:13 AM
To: Roberts, Jon
Cc:
I have never seen order by in a delete statement in Oracle so I tested it.
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 25 07:45:50 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> create table a (a1 number, a2 number);
Table created
ssage-
From: Robert Treat [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 24, 2007 10:24 PM
To: pgsql-general@postgresql.org
Cc: Roberts, Jon
Subject: Re: [GENERAL] subversion support?
On Wednesday 24 October 2007 15:11, Roberts, Jon wrote:
> Yeah. I think having to save the function to disk
there so you can see what the differences are.
Jon
-Original Message-
From: brian [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 24, 2007 1:59 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] subversion support?
Roberts, Jon wrote:
> Are there plans to support a plug-in t
Are there plans to support a plug-in to a version control system like
subversion for DDL? We really need to version our functions we will be
writing but currently, we have to go through some hoops to get the function
code into subversion.
Jon
I need to install the pgAgent daemon on Solaris but I don't see a binary for
download except for earlier versions. How soon will v1.8 for Solaris be
available in binary format?
Thanks!
Jon
73 matches
Mail list logo
