--- ow <[EMAIL PROTECTED]> wrote:
> How? The doc only mentions db: pg_dump [option...] [dbname]
>
> Then, how would I lock users out from the schema while it's being loaded?
Never mind how, I see there's "-n namespace" option in 7.4. But still, how
would I l
--- Jan Wieck <[EMAIL PROTECTED]> wrote:
> #!/bin/sh
>
> (
> echo "start transaction;"
> cat $2
> echo "commit transaction;"
> ) psql $1
>
>
>
> then call it as
>
> reload_in_transaction my_db my_namespace.dump
>
> Since the whole dump will be restored inside of one transaction
Hi,
Is there a way to specify that all tables should be created WITHOUT OIDS by
default?
Thanks
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
---(end of broadcast)-
pgSql 7.4.0
Hi,
Am trying to find duplicate values in a large table (about 80M records).
Somehow, none of the two (2) queries (see below) is using the index "I_bca"
that, I believe, should've been used.
Any ideas? Thanks
--
CREATE
--- Joe Conway <[EMAIL PROTECTED]> wrote:
> >
> >>explain select b, c, a
> >>from test
> >>group by b, c, a
> >>having count(*) > 1
>
> Why would you expect this to use an index scan when it needs to read the
> entire table? If you read the whole table (or even a significant
> fraction of it),
PostgreSQL 7.4 on i386-linux-gnu, compiled by GCC 2.96
-- about 10 records
CREATE TABLE my.Small
(
id my.dint NOT NULL,
code my.dvalue NOT NULL,
CONSTRAINT pk_1 PRIMARY KEY (id),
) WITHOUT OIDS;
-- about 80M rows
CREATE TABLE my.Large
(
id my.dlong NOT NULL,
small_id my.dint NOT NULL,
--- Stephan Szabo <[EMAIL PROTECTED]> wrote:
> Hmm, I'd wonder if maybe it's choosing a sequential scan in the second
> case? As a random question, does increasing the statistics target on
> Large.small_id and re-analyzing change its behavior?
Ran analyze, the result is the same. Here's more inf
--- Stephan Szabo <[EMAIL PROTECTED]> wrote:
> You also did the alter table to up the statistics target on the column,
> right?
Not really. I did not change the the default stats settings in the
postgresql.conf. Not sure what needs to be changed, can you clarify?
Thanks
_
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> ow <[EMAIL PROTECTED]> writes:
> > When I try to delete record, it takes > 3 min.
>
> I think it must be using a seqscan for the foreign key check query.
> Could you try this and show the results?
1) SELECT 1 FROM ON
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Well, there's the smoking gun all right. Why does it think there are
> going to be 7893843 matching rows!? Could we see the pg_stats row for
> the large.small_id column?
>
> regards, tom lane
schemaname tablename attnam
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> According to this entry, your small_id column only contains the ten
> values 1..10, roughly evenly distributed. So why are you probing for
> 239??
Let's say we have City (small) and Person (large) tables. A new city was added
(mistakenly) with id=239, it
Hi,
Is there a way to retrieve the current value (i.e. the last used value) of the
last used sequence without specifying the sequence name? Something like the
following:
create table ( id serial, value varchar(10));
insert into someOthertest (value) values ('100');
insert into test (value) values
--- Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
>
> You could use the same sequence in many tables.
>
I guess I could but then:
1) I'd still have to know the sequence name. When integrating pgSql with 3party
frameworks I'd either have to pass it there (could be a very difficult task) or
make
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> or that the last one that's touched is the one you want?
Correct.
__
Do you Yahoo!?
Yahoo! Search - Find what youre looking for faster
http://search.yahoo.com
---(end of broadcast)---
Hi,
This issue appears to be present in 7.4.2 Am not sure if the plan was to fix
it or leave like this. Any ideas? Thanks
See http://archives.postgresql.org/pgsql-sql/2004-02/msg00104.php
or
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=20040212140516.65859.qmail%40web60807.mail.y
>
> Hi,
>
> This issue appears to be present in 7.4.2 Am not sure if the plan was to fix
> it or leave like this. Any ideas? Thanks
>
> See http://archives.postgresql.org/pgsql-sql/2004-02/msg00104.php
Never mind. I think it works. Thanks
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Can't see how we optimize your case without pessimizing more-common cases.
My case appears to be pretty common, i.e. 1 small and 1 large table with RI
constraint between them. In order to delete a record from the small table, the
large table must not have
Hi,
Am looking for a way to minimize the amount of fuctions that support triggers.
E.g., there's "company" and "company_backup" tables. Update trigger on the
"company" table will put a record in the "company_backup" table whenever
"company" record is updated.
The problem is that there's quite a f
--- Peter Childs <[EMAIL PROTECTED]> wrote:
> Yes it is possible and I've done it. The reason I'm not using it is because
> I
> wrote it in Pl/Python and if you attach the same trigger to more than one
> table in the same transaction pg/python (actually the entire server crashes
> but that
Hi,
Suppose, one server/postmaster is running several databases. Is there something
like "SET database TO whatever" (and I mean *database* not schema). I need this
to ensure that a script runs only against db for which it was desinged.
Thanks
__
Do you Yahoo
--- Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
[...]
> dynacom=# \c bab
> You are now connected to database bab.
> bab=#
I'm actually looking for a way to set database from a *script*, not psql
console, if this is possible (similar to "SET search_path TO whatever;").
Any ideas? Thanks
Hi,
I had a look at "create type" docs and it seems somewhat complex, involving
creation of functions and etc. I hope there's a simpler way for the following:
How should one declare a new custom type, say, "AddressType" that corresponds
internally to "varchar(50)". In other words, all columns tha
--- ow <[EMAIL PROTECTED]> wrote:
> Hi,
[...]
> How should one declare a new custom type, say, "AddressType" that corresponds
> internally to "varchar(50)". In other words, all columns that are assigned
> "AddressType" would internally be "
--- Rod Taylor <[EMAIL PROTECTED]> wrote:
> Only one keyword off. SQL calls this a domain.
>
> They're limited in 7.3, but much improved (not yet perfect) for 7.4.
>
> http://www.postgresql.org/docs/7.3/interactive/sql-createdomain.html
Excellent news! Thanks
__
Hi,
1) When psql is run from a script, how would one save *all* output generated by
psql (including errors, info messages, etc) in to a file?. I tried redirecting
output with ">" but that did not save error/confirmation messages.
2) When psql is run from a script, how would one pass a password to
Pg 7.3.3 on i386 compiled by GCC 2.96
Hi,
It's understood that FK constraints carry some performance hit. However, the
performance hit I observe is huge. My situation is illustrated by the table
structures below.
Parent table has 20,000 rows and Child table has about 60,000.
Without fk_child_pa
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> It looks to me like the 7.3 planner will not choose indexscans for the FK
> check queries in this example, because the comparison operators are
> misconstrued as shown in this thread:
> http://archives.postgresql.org/pgsql-hackers/2003-03/msg00997.php
> Th
Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06.
All configuration settings are default.
Hi,
Trying to find a way to improve range query performance.
The table Test has about 30 million records.
-- DLong, Dtimestamp, Dint, etc are domains of the respective types.
create table Test (
i
gt; wrote:
>
> Try CLUSTER --- that usually helps with index scans on ranges.
>
> ---
>
> ow wrote:
> > Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06.
> > All configuration settin
--- Stephan Szabo <[EMAIL PROTECTED]> wrote:
> I'm not sure that AK_abc is the best index for check a range on a and
> single values on b and c. I'd think that something like an index
> on (b,c,a) would probably be better for this purpose (without doing any
> testing ;) ).
That would not work fo
--- Bruce Momjian <[EMAIL PROTECTED]> wrote:
> Strange 30 records takes 30x the time than one record. Can you run
> ANALYZE and send us an EXPLAIN of the query to make sure it hasn't
> changed?
>
explain analyze select * from Test
where a >= '2002-06-18'
and a <= '2002-07-18'
and b = 5
and
--- Bruce Momjian <[EMAIL PROTECTED]> wrote:
> Tom Lane wrote:
> > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > Sorry there isn't a simple solution.
> >
> > But there is: make an index with the column order (b,c,a).
>
> Oh, yea, right. If he puts the columns he is doing a straight equals
> c
Hi,
Suppose pgSql server is running databases "db1" and "db2" that are in the same
db cluster. A client app establishes connection "conn1" to db1. Is there a way
to programatically switch conn1 to use db2 without doing
disconnect-from-db1-connect-to-db2? Something like what "\c" does but to be
use
--- Peter Eisentraut wrote:
> Nothing prevents you from keeping the connection to db1 open when you open
> a connection to db2. By the way, psql's "\c" command does exactly
> disconnect-from-db1-connect-to-db2.
That's the whole point: I'm trying to avoid maintaining *separate* connection
pools
--- Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> I'm afraid that what you want to do is not possible. Perhaps you want to
> organize your data into schemas, not databases.
There's too much data to put it in one db. If anything happens to it, I'll
never be able to restore (or dump) it in time. B
--- Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> I'm afraid that what you want to do is not possible. Perhaps you want to
> organize your data into schemas, not databases.
There's too much data to put it in one db. If anything happens to it, I'll
never be able to restore (or dump) it in time. B
--- Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> You could just dump individual schemas.
How? The doc only mentions db: pg_dump [option...] [dbname]
Then, how would I lock users out from the schema while it's being loaded?
Thanks
__
Do you Yahoo!?
Protect y
Hi,
We are considering moving some reports from *** to Postgres. Our reports are
written as stored procs in Transact-SQL and usually have the following
structure:
CREATE PROCEDURE someReportProc AS
/* Purpose: Creates a report based on Table1.
*
* Overview of what will be done:
* 1) create a
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> If you need some procedural logic (if-then-else stuff)
Yes
> then you'd need
> to go over to plpgsql, which would be a bit of a pain because its habit
> of caching query plans doesn't play nice with temp tables.
Going to plpgsql is fine. What I'm tryin
39 matches
Mail list logo
