Richard Huxton wrote:
Sebastien FLAESCH wrote:
Hi,
Parameterized queries (PREPARE/EXECUTE), is a great thing, but it
would even be better if the DECLARE CURSOR could use a PREPAREd
statement, to declare a "server-side" cursor with a parameterized
query, to use the benefits of DECLAREd cursors (n
Did anyone get multi-column GIST indexes working using both
the gist_btree and postgis modules?
It must.
fl=# -- sessionid is a text; the_geom is a GEOMETRY
fl=# create index testidx2 on user_point_features using gist
(sessionid,the_geom);
server closed the connection unexpectedly
This pr
Hi,
I've gone through the list of replication solution on the PostgreSQL
website but I think I'm looking for something rather specialised.
The situation is that there are little satellite machines around the
place, seperated by public internet, each writing to their own log
tables. What I want is
Hi Tatsuo / Tom,
[TI]
> Apparently your hack does not kill #define USE_WIDE_UPPER_LOWER.
Mmm, I think it does, but mind you, the hack was applied to the first machine
only (since that was the one with the 'original' buggy glibc causing a
postmaster crash when using upper() and stuff), while it
In the pg_hba.conf file, I have the following entry:
hostdb1client1192.168.150.234/32md5
Now the problem is:
The client having IP 192.168.150.234 is trying to
use phpPgAdmin, but is unable to connect.
The corresponding config.inc.php has this entry:
$conf['servers'][0]['desc'
On Mon, 28 Feb 2005, Martijn van Oosterhout wrote:
On Mon, Feb 28, 2005 at 01:46:16PM -0600, [EMAIL PROTECTED] wrote:
Hello!
Clay Shirky made a comment about MySQL that I thought the PostgreSQL
community should be aware of:
http://www.shirky.com/writings/situated_software.html
It's the section (
Sumit Rohatgi wrote:
In the pg_hba.conf file, I have the following entry:
hostdb1client1192.168.150.234/32md5
Now the problem is:
The client having IP 192.168.150.234 is trying to
use phpPgAdmin, but is unable to connect.
The corresponding config.inc.php has this entry:
$conf['
Hi,
I have created a btree index on a 'int4' attribute of a table.
After i have inserted 1,000,000 raws in my table, i can see that my index
size is 2745 Blocks (8KB each) from pg_class. That means about 21,960 KB
size.
I try to understand hows is this number generated, because thought that
fo
Please disregard my previous mail
In the pg_hba.conf file, I have the following entry:
hostdb1client1192.168.150.234/32md5
Now the problem is:
The client having IP 192.168.150.234 is trying to
use phpPgAdmin, but is unable to connect.
The corresponding config.i
Hi All,
I want to spend less time coding and more time running queries against
the DB which i am building. What about QT from TrollTech!! Can that be
used to whip up gui real fast! Dreamweaver?
Hrishi
On Tue, 01 Mar 2005 07:55:04 +, Richard Huxton wrote:
> Hrishikesh Deshmukh wrote:
> > A
If you are into perl, you should definitely look at Class::DBI
(http://www.class-dbi.com) which is freely available from CPAN (despite
the .com site). It treats table rows as objects with methods. It can
model foreign key relationships based entirely on the database schema
(and entirely autom
Hrishikesh Deshmukh wrote:
Hi All,
I want to spend less time coding and more time running queries against
the DB which i am building. What about QT from TrollTech!! Can that be
used to whip up gui real fast! Dreamweaver?
Neither of these are really application tools.
Give "Ruby on Rails" a look - t
celerity12 wrote:
Please disregard my previous mail
In the pg_hba.conf file, I have the following entry:
hostdb1client1192.168.150.234/32md5
Now the problem is:
The client having IP 192.168.150.234 is trying to
use phpPgAdmin, but is unable to connect.
Make sure co
On Tuesday 01 March 2005 08:07 am, Hrishikesh Deshmukh wrote:
> Hi All,
>
> I want to spend less time coding and more time running queries against
> the DB which i am building. What about QT from TrollTech!! Can that be
> used to whip up gui real fast! Dreamweaver?
>
> Hrishi
You could use gnue-de
> I have created a btree index on a 'int4' attribute of a table.
>
> After i have inserted 1,000,000 raws in my table, i can see that my index
> size is 2745 Blocks (8KB each) from pg_class. That means about 21,960 KB
> size.
>
> I try to understand hows is this number generated, because thought
I am building a generic crosstab function in plpgsql, where I can pass in a
SQL statement and return back the data pivoted with the values of one column
serving as headers of the return columns. If I use a RECORD variable to
scroll through the data, is there a way for me to tell the number o
Thanks a lot.
An other question:
Is there any way to prevent duplicates on btree index attribute,
PERMITTING them on table?
On Tue, 1 Mar 2005, Tatsuo Ishii wrote:
> > I have created a btree index on a 'int4' attribute of a table.
> >
> > After i have inserted 1,000,000 raws in my table, i
Don't forget to cc the list.
celerity12 wrote:
I turned on the connection logging:
Server IP is 192.168.160.213
Client IP is 192.168.160.212
Its generating the entry for client in the log file
but the IP of client is different( .212 not .213 ...
.213 is server's IP)
Getting this message:
2005-03
I tried looking into your solution.. However, the "DEFERRABLE
INITIALLY DEFERRED" doesn't seem to act as I expect it to. I made two
sample tables:
CREATE TABLE functions.temp1
(
id1 int4 NOT NULL,
id2 int4,
CONS
Mike Preston wrote:
I am building a generic crosstab function in plpgsql, where I can pass
in a SQL statement and return back the data pivoted with the values of
one column serving as headers of the return columns. If I use a RECORD
variable to scroll through the data, is there a way for me to tel
On 2/28/05 6:53 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
> If you are suffering bloat, the fastest route to a solution would
> probably be to CLUSTER your larger tables. Although VACUUM FULL
> would work, it's likely to be very slow.
How can there be bloat if there are no deletes or modifies?
On 2/28/05 6:53 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
> It's hard to see how the vacuum time wouldn't
> be linear in table size if there's nothing to do and no dead space.
I am doing 'vacuum analyze' rather than just 'vacuum'. Could that have
anything to do with the non-linear behavior?
Wes
> Ron Mayer wrote:
> > Did anyone get multi-column GIST indexes working using both
> > the gist_btree and postgis modules?
Multi-column gist indexes are basically useless at this point. The index pages
are split based entirely on the first column, so the index becomes basically
an index on the fi
> > There are 5 vaccinations in a given vaccination schedule.
> >
> > Patient had 3 shots.
> >
> > I want the view to show me that shot 4 and 5 are missing
> > without having to enter the cardinality of the vaccination in
> > the original data.
>
> For this kind of task you usually want to use a
> > There are 5 vaccinations in a given vaccination schedule.
> >
> > Patient had 3 shots.
> >
> > I want the view to show me that shot 4 and 5 are missing
> > without having to enter the cardinality of the vaccination in
> > the original data.
>
> That sounds like you are trying to abuse the data
On a 128MB RAM, 450 MHz pentium 3 server with linux gentoo and postgresql
7.4.6 on an office lan we can manage satisfactorily a db containing few
tables with a cumbersome amount of data (each table is around 650 thousand
records with 98 columns) relating to the year 2002.
We use M$-Access and ODBC
On Tue, 1 Mar 2005 17:48:44 +0200, Vitaly Belman <[EMAIL PROTECTED]> wrote:
>
> CREATE TABLE functions.temp1
> (
> id1 int4 NOT NULL,
> id2 int4,
> CONSTRAINT pk_temp1 PRIMARY KEY (id1),
> CONSTRAINT temp2_id2
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> ... Now the number becomes 1967+7 = 1974. Still it's different from
> 2745. If you don't have deleted tuples, the difference probably comes
> from the fact that a btree index can never be 100% occupied. IMO
> 1974/2745 = 0.71 seems not so bad.
In fact the
I figured it out, maybe is not the most elegant way but it work for my
case where only small sets are retrieved
create table foo2 (pk int, valor numeric(12,2), porce numeric(5,2));
insert into foo2 values (1,7893.45,0.4);
insert into foo2 values (5,7893.45,0.3);
insert into foo2 values (9,7893.45
On Mon, Feb 28, 2005 at 09:27:46PM +0100, Martijn van Oosterhout wrote:
> On Mon, Feb 28, 2005 at 01:36:59PM -0600, Jim C. Nasby wrote:
> > > I used a straight copy of the filesystem with running database
> > > (over the net in my case) and immediately after that,
> > > stop the db and rsync for th
Is anyone currently using pgpool with production high volume use? I am
interested in your success and challenges.
Brian
I am passing a SQL statement to a function as a parameter and then
executing it via a call like
OPEN curs1 FOR EXECUTE sql_str;
Since I won't know in advance the fieldnames being passed by the SQL
string, I'd like to dereference the column fields by position. Can anybody
tell me the syn
I am new to postgresql, having previously worked with mysql mostly.
What kind of command would I run if I wanted to copy an entire table
(along with renaming it, and, of course, all data from the first table -
some of which is binary)?
Thanks,
-Mike
---(end of broadcast)-
Sorry, I meant to ask about copying databases, not tables (including all
data in the database as per below).
Thanks,
-Mike
Michael Romagnoli wrote:
I am new to postgresql, having previously worked with mysql mostly.
What kind of command would I run if I wanted to copy an entire table
(along with
On Mar 1, 2005, at 4:23 PM, Michael Romagnoli wrote:
I am new to postgresql, having previously worked with mysql mostly.
What kind of command would I run if I wanted to copy an entire table
(along with renaming it, and, of course, all data from the first table
- some of which is binary)?
Thanks,
"Mike Preston" <[EMAIL PROTECTED]> writes:
> Since I won't know in advance the fieldnames being passed by the SQL
> string, I'd like to dereference the column fields by position. Can
> anybody tell me the syntax for doing this?
There isn't one, at least not in plpgsql. You might have some succes
I want to return all records that match criteria across three separate
tables
and , in spite of reading up on joins, have so far been unable to
design a
solution that doesn't require caching a hash table of intermediate
results.
Here's the situation:
Let's say color names belong to a set of ton
Sean Davis wrote:
On Mar 1, 2005, at 4:23 PM, Michael Romagnoli wrote:
I am new to postgresql, having previously worked with mysql mostly.
What kind of command would I run if I wanted to copy an entire table
(along with renaming it, and, of course, all data from the first table
- some of which is
Get a better computer to run it on in teh long-term -- that will be your
best investment
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On a 128MB RAM, 450 MHz pentium 3 server with linux gentoo and postgresql
> 7.4.6 on an office lan we can manage satisfactorily a db c
On Tue, 01 Mar 2005 16:30:19 -0500, Michael Romagnoli
<[EMAIL PROTECTED]> wrote:
>
> Sorry, I meant to ask about copying databases, not tables (including all
> data in the database as per below).
you can do a pg_dump your_database>your_database.dump.sql
and then createdb to create your new datab
Is it possible to setup a trigger so that every time a
certain field is changed, an email is sent? Using
pl/pgsql.
Thanks,
CSN
__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
---
On Tue, 2005-03-01 at 13:42 -0800, Scott Frankel wrote:
> [snip problem]
> Task: find all color names in each of palette1's tones.
>
> Can this be done in a single SQL statement?
> [snip table examples]
looks like a job for NATURAL JOIN
test=# select color_name
from palettes
create database newdb template olddb;
works as well.
-Chris
On Tuesday 01 March 2005 05:08 pm, javier wilson wrote:
> On Tue, 01 Mar 2005 16:30:19 -0500, Michael Romagnoli
>
> <[EMAIL PROTECTED]> wrote:
> > Sorry, I meant to ask about copying databases, not tables (including all
> > data in th
On Fri, 25 Feb 2005, Dave Smith wrote:
> Is a portal using the V3 protocol the same as a cursor? I am trying to
> debug a slow query and I want the JDBC driver to use the cursor syntax.
> It says it is using a portal but the performance seems like just a
> regular sql statement.
>
Yes, portal
[EMAIL PROTECTED] (CSN) writes:
> Is it possible to setup a trigger so that every time a
> certain field is changed, an email is sent? Using
> pl/pgsql.
Something _like_ that is possible.
What I would do instead is for the trigger to cause a record to be put
into a table that might be called some
Hello,
The first time I run a query against this function (using Npgsql):
declare r_cursor1 cursor for
SELECT * from tmp_table;
begin
CREATE TEMPORARY TABLE
tmp_table
(
testcol integer ) ON COMMIT
DROP;
INSERT INTO
tmp_table
"L. Fletcher" <[EMAIL PROTECTED]> writes:
> I get this error:
> ERROR: XX000: relation 1090457025 is still open
This is an 8.0 bug fixed in 8.0.1.
> All subsequent times I get this error:
> ERROR: 42P01: relation with OID 1090457025 does not exist=20
This is because plpgsql caches plans and ther
Christopher Browne wrote:
[EMAIL PROTECTED] (CSN) writes:
Is it possible to setup a trigger so that every time a
certain field is changed, an email is sent? Using
pl/pgsql.
Something _like_ that is possible.
you can also do it directly with a trigger if you prefer:
CREATE TRIGGER alert_i
Sweet! And not so sweet.
The natural join worked beautifully with my test schema; but it failed
to yield any rows with my real-world schema. I think I've tracked down
why: duplicate column names. i.e.:
-1- these tables yield rows from a NATURAL JOIN query
CREATE TABLE palettes (palette_
hi all,
per the docs, i understand i *can* define/change the global pgsql debugging
level at the cmd line by passing, say, pg_ctl the '-o "-d 5"' option,
AND,
that i can finely change loglevel options in postgresql.conf, e.g.:
client_min_messages = debug5 # debug5, debug4, debug3, debug2, de
> Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> > ... Now the number becomes 1967+7 = 1974. Still it's different from
> > 2745. If you don't have deleted tuples, the difference probably comes
> > from the fact that a btree index can never be 100% occupied. IMO
> > 1974/2745 = 0.71 seems not so bad.
>
> An other question:
>
> Is there any way to prevent duplicates on btree index attribute,
> PERMITTING them on table?
I can't think of any usefull usage for such an index. Can you explain
why you need it?
--
Tatsuo Ishii
---(end of broadcast)---
TI
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
>> ... rather it happens because the CREATE INDEX command
>> deliberately loads the index leaf pages only 2/3rds full, to avoid a
>> disproportionate amount of page splitting when normal inserts commence.
> Interesting. Right after CREATE INDEX for a int4 c
> > Interesting. Right after CREATE INDEX for a int4 column using pgbench
> > -s 10(1,000,000 tuples), I got 2184 leaf pages. From my caliculation
> > the number of leaf pages is expected to 1965, which is 100% full case
> > assumption of course. So 1965/2184 = 0.8997 = 90% is actually used?
>
> S
Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> So it seems Ioannis' number was not taken immediately after a CREATE
> INDEX operation?
I would guess not, but it's up to him to say. If it is a number derived
after some period of normal operation, then his result agrees with the
theory that says 70% is
Hi,
I am new to postgresql!
We have a M$SQL server and would like to do a replication from this
server to postgresql.
Would this be possible?
If so, what would be the appropiate method.
Any suggestion?
Thx!
---(end of broadcast)---
TIP 5: Have yo
Hello,
The first time I run a query against this function (using Npgsql):
declare r_cursor1 cursor for
SELECT * from tmp_table;
begin
CREATE TEMPORARY TABLE
tmp_table
(
testcol integer ) ON COMMIT
DROP;
INSERT INTO
tmp_tabl
Hello there,
i have a short question ...
I have a few tables (at the moment "only" 3 for testing), over which
will by made a query in this form:
SELECT
a.orderitem_id,
a.transaction_id,
a.order_id,
a.shop_id,
a.quantity,
a.price,
b.affiliat
Envbop wrote:
Can someone tell me where I can find the database names.
login using psql and type \l ;-)
chances are you have root access to the linux box, then you could modify
pg_hba.conf to gain some access
Do you have any account information at all?
---(end of broadcast
[EMAIL PROTECTED] (Michael Romagnoli) writes:
> What kind of command would I run if I wanted to copy an entire table
> (along with renaming it, and, of course, all data from the first table
> -
> some of which is binary)?
SELECT * INTO newtable FROM oldtable;
Note that this doesn't construct ind
>From the docs:
"FM suppresses leading zeroes and trailing blanks that would otherwise be
added to make the output of a pattern be fixed-width"
It works now but for one I don't understand why the space is added in the
firs place and two I think the docs don't tell the whole story ie leading
bl
Hi
I've just inherited a PostgreSQL database, for which I do not have any
details of, like database name or the users.
This used to be a library database which was managed via a web page written
in php.
Its running on a Linux box.
The front end was also written in php. The original writers of this
On Tue, 2005-03-01 at 16:51 -0800, Scott Frankel wrote:
> Sweet! And not so sweet.
>
> The natural join worked beautifully with my test schema; but it failed
> to yield any rows with my real-world schema. I think I've tracked down
> why: duplicate column names. i.e.:
> ...
> CREATE TABLE
Steffen Boehme <[EMAIL PROTECTED]> writes:
> FROM
> ss_order_orderitems a
> LEFT JOIN ss_order_affiliate_tracking b ON a.order_id = b.order_id,
> ss_shops c
> WHERE
> (a.order_id = b.order_id OR b.order_id IS NULL) AND
What is that last line doing there? It's completely redunda
PostGreSQL 8.0beta1
I have a function that uses a cursor and it is giving me the error: cursor
"crsr" already in use when the parameters I pass in come from another table.
The function works fine when I call it by itself, such as select
PartNeedsReschedule(100,1) or select * from PartNeedsReschedu
65 matches
Mail list logo
