nd that, it only needs to be a superior product.
Of course when someone /does/ know PostgreSQL, it's usually a sign that
they have more than a passing familiarity. I wonder how many MySQL
admins are on the same level of proficiency as Windows admins due to
ubiquitity.
Gregory Wood
-
Does dbmirror do that? No, it does not. It also doesn't support
promoting a slave database to a master; that has to be done manually, so
I wouldn't consider that too big a problem.
Worse in my opinion is that sequences don't get updated... so a slave
that tries to do an insert on a replicated t
Jan Wieck wrote:
If you don't know the answers to that, I assume it isn't that easy as
people try to make believe. And in case the answer is "that is not
possible but ...", then you better think again what you want that
replication setup for.
Although I agree with your points (especially having
Stephane Tessier wrote:
Hi,
I have a problem with triggers in postgresql 7.3.4 and PHP... I have a lot
of insert to do from a table x to table y ( average of 1 rows each
time). I use a trigger AFTER INSERT but it seems that PHP wait for the
result of the trigger to ending the script... Is it p
I have a "comment" field in a table that I want populated if another field
has a certain value. Is it possible to set a check constraint for this?
Example:
Let's say we have fields
Purchase_type smallint check(purchase_type <4)
comment varchar
I need a check rule to something like (pseudo cod
Mike Nolan wrote:
However, if I update table 'B' and the 2nd trigger fires, that trigger
will still see the OLD value if does a query on table 'A', since I
think transaction atomic rules require that any updated values aren't
made available to the outside world (including other triggers) until th
Further pointers are welcome, otherwise if I figure it out myself, I'll
post the results.
Just an opinion here, but I would implement these queries as views
similar to the information schema. That way you won't have to port
everything when the database changes. Once you upgrade to 7.4 you can
ju
Perhaps a combination of those settings is what did it?
Maybe --- what was the failure message exactly, once you got it?
Managed to fix it before I got any errors actually. Simple enough to
re-break it however:
FATAL: max_fsm_pages must exceed max_fsm_relations * 16
Looks like I was 6 short
> Yeah, I know. I was just trying to defend mysql. ^_^ We use both, and so
> far, it's been the smaller headache, so...
That may be true... until you have to implement transactions and/or foreign
keys at the application level.
> The really strange thing is, one of our newwer databases has
> st
> key constraint is ON DELETE NO ACTION, which confusingly
> enough aborts the transaction (it's defined that way in the
> SQL standard, don't ask me why they called it NO ACTION).
NO ACTION because it takes no action on the child record? Makes sense when
you consider that
> >But the question itself tells that you're about to implement
> >a major design error in your application. Holding database
> >locks during user interaction IS A BAD THING. Never, never
> >ever do it that way. And anybody telling you something
> >diffe
> Any suggestions would be more then appreciate as always. Is there a
> better way to do what I'm trying to do?
I would recommend using only one sequence for the master table. Then just
reference that sequence value for the two foreign keys. For example:
BEGIN;
INSERT INTO Bugs (bug_date) VALUE
This code looks fine to me, other than missing the actual trigger statement
is missing. Assuming table 1 is named apps:
DROP TRIGGER OnApplicationsDelete ON apps;
DROP FUNCTION ApplicationsDeleteFn();
CREATE FUNCTION ApplicationsDeleteFn()
RETURNS OPAQUE
AS '
BEGIN
delete from ports where appNam
> Can you please tell me, how to do this in postgresql
Use a SERIAL datatype:
http://postgresql.crimelabs.net/users-lounge/docs/7.1/user/datatype.html#DAT
ATYPE-SERIAL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http:
> A direct query gets appropriate rows of data:
>
> dbname=# select * from partdef where shpname = 'IDC16W';
> ...while the very same query (substituting LIKE for the '=' sign) gets
nothing!?
>
> dbname=# select * from partdef where shpname LIKE 'IDC16W';
> Can someone please tell me the really
> Inside a transaction,
>
> I insert a row into the table, with an autonumber id.
> Now I want to return the id of the newly inserted row. How can I do
> that?
>
> It seems that the first insert statement is not committed, and so the
> second statement couldn't return the newly id.
SELECT currva
> > ... So anytime I see something that requires some sort of shell access
> > I don't like it. EXPLAIN is one of those things.
>
> Huh? EXPLAIN doesn't require shell access ... it merely requires a
> client that doesn't drop NOTICE messages into the bit bucket.
>
> Clients that drop NOTICEs ar
> Then, I just have a telnet/ssh/etc session on the server running :
> Would this be useful to you?
Seem just as easy to run psql to me. In fact a bit easier as I don't have to
switch back and forth between windows.
I guess it goes back to the days when I was first learning SQL on a MySQL
web d
> When the name of a sequence auto-generated from a SERIAL type would be
> longer than 32 chars, it appears that "CREATE TABLE" uses a different
> name truncation algorithm than "DROP SEQUENCE". Example below. Note
> the difference between the following:
>
> 'process_state_subscripti_id_
I'm partial to the ZEOS Database Explorer myself. I don't have experience
with a multitude of tools, so the best I can offer is that I have no
problems with the latest version :) Actually, I can tell you that it has the
advantage of a native interface (rather than relying on ODBC). And it's
free,
> Could u just tell me if it is possible to have a Windows Client and the
Server running on Linux and having Postgres and the two communicate thru
something like the ODBC.
Yes, although it's preferable to use a native library.
> Does Postgres have anything for the advanced features like OleDB in
> I am generating scripts from MSSQL Server and converting them to create
objects in PostgreSQL. It is suprisingly easy. However, I think I may have
hit a rock.
>
> It appears that PostgreSQL does not support listing constraints to be
added as in the following syntax:
I don't know why it won't
> I have a table with approx. 2mln records.
>
> There were a few for which I had to update statusfield, so I did:
>
> update table set statusflag = 'U' where id in ('id10', 'id20',
> 'id30');
>
> this took so long that I cancelled it, and used separate
>
> update table set sta
> This table currently has 224 rows of data in it.
>
> The following queries *ALL* take approx. .433 seconds to run.
>
> select * from status s where s.site_id = 18 and s.host_id = 49 and
> s.product = 'BETA' and s.class = 'APPS' and s.subclass = 'MONITOR' ;
> select * from status s where s.site_
I have a plpgsql update trigger on one of my tables. The intention of the
trigger is to check to see if a particular field is updated, and if so
INSERT a record into another table. The IF statement looks something like
this:
IF new.MyField <> old.MyField THEN
This works great when MyField has an
> What I'd like to do is pull a list of records where there is a range
> of last names; say from A - F.
> select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%'
> - for example.
>
> The above code I've tried for this doesn't seem to work as I'd expect
> it too?
When you use the AND
Ahhh, but I didn't use a double quote identifier. This statement worked fine
for me:
CREATE TABLE atable (
title VARCHAR(20),
name VARCHAR(20),
id SERIAL PRIMARY KEY,
date DATE);
Greg
- Original Message -
From: "Joel Burton" <[EMAIL PROTECTED]>
To: "Gregory Wo
> I don't know if you can name a column "date" because I think it's a
> reserved word.
Oddly enough, it *does* work (at least on my version of 7.1), although I
would recommend against doing it if for no other reason than it's confusing.
Greg
---(end of broadcast)---
> > I'm surprised that works at all... the name of the table is "atable",
isn't
> > it? Try this:
> >
> > INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
> >
> > Greg
>
> Thanks for the help. I wrote the command out wrong in the post, I did try
the
> one that you wrote out
> Can anyone tell me what this notice means?
>
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s) ?
It means that PostgreSQL will automatically create triggers to perform your
foreign key checks :)
Seriously though, PostgreSQL has to have some mechanism to check t
3) I've been told PostgreSQL databases must be periodically "rebuilt" so to
speak, ("vacuumed"). Is this easy to do on-the-fly? Rapid? Or, does the db
need to go down awhile?
Not so much rebuilt, but just cleaned up. My understanding is that deleted
records (and outdated copies of UPDATEd records
This has been discussed quite a few times recently (in fact, I posted a
similar reply yesterday). From Tom Lane:
"Change php_pgsql.h to refer to postgres_fe.h not postgres.h."
Greg
- Original Message -
From: "Gordon A. Runkle" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday,
To the best of my knowledge, you can't use parameters for table names. But
if you have 7.1, you can use the EXECUTE command to dynamically generate
(and execute) any SQL statement. Check out:
http://postgresql.readysetnet.com/devel-corner/docs/programmer/plpgsql-descr
iption.html
Specifically: 2
> > I am pretty sure that pg_dump produces a consistent snapshot, using
> > the same transaction isolation mechanism (MVCC) as the database
> > itself. If one of the gurus posts and says otherwise, believe him,
> > not me. ;)
>
> My impression from reading the source was that it was consitant pe
> SELECT sum(grade) / count(grade) As GPA FROM grades;
> (bad juju if 0)
No kidding... that kid totally failed ALL his classes! And before someone
points it out, yes I saw the DBZ.
Greg
---(end of broadcast)---
TIP 1: sub
You didn't mention which version either of those machines are running. I
believe in 7.1 Postgres the database filenames changed to a numerical
database id of some sort. Prior to that, they were named the same as the
database. I don't know the reasoning behind the switch, but I'm fairly sure
it was
> > > I have a table and want to make a view updateable with rules. Table
has a
> > > sequence that creates default value to primary key when inserting.
> > >
> > > And now the question: Is it posible to return value of primary key
from
> > > rule of inserting with NEW? Is that value what i wo
> I have a table and want to make a view updateable with rules. Table has a
> sequence that creates default value to primary key when inserting.
>
> And now the question: Is it posible to return value of primary key from
> rule of inserting with NEW? Is that value what i would get from extern
> Boulat Khakimov <[EMAIL PROTECTED]> writes:
> > Here is a nifty query I came up with
> > that provides a detailed information on any row of any table.
> > Something that is build into mySQL (DESC tablename fieldname)
> > but not into PG.
>
> Er, what's wrong with psql's "\d table" ?
Those witho
43 hours? Ouch, that is quite a long time! I'm no expert by any means, but
here are a few tips that I've picked up on this list that might help out:
1. The COPY command is blazing fast for importing, if you are certain your
input is clean, this is the way to go. Read more about that here:
http://
Using a DateTime value in a WHERE clause is not a good idea for the reason
that DateTimes are usually represented by a floating point value in the
database itself. And since floating point numbers are prone to rounding
errors, they don't make for a good unique identifier.
If you have to use dates
Oh, I just made sure that I started a new transaction :)
I actually prefer that timestamps are handled that way... once I realized
*why* I had triggered INSERTs that were stamped 45 minutes earlier than I
thought they should have been.
Greg
> Yes, and that's a feature :)
>
> If you want a wall
> columns with default timestamp('now') see to be
> defaulting to the time I started posgresql!
I noticed that timestamps (in my case CURRENT_TIMESTAMP) are taken from the
beginning of a transaction. You didn't mention how you were accessing the
database, but if you were updating everything insid
> I am trying to design a database in SQL Server ver7, using
> Can anyone PLEASE HELP??
Possibly, but you'd be more likely to find an answer on an SQL Server
mailing list. This list is for the PostgreSQL database.
Greg
> You can detect whether a field is actually being *changed* by comparing
> NEW.field and OLD.field. You cannot distinguish the case where the
> UPDATE didn't mention a field from the case where it did but assigned
> the same value that was already there (eg UPDATE foo SET x = x).
> This behavior
We recently upgraded our development server to 7.1 and I believe I've
noticed an inconsistency with how update triggers behave on version 7.1
versus 7.0. Since I'm not sure which should be the expected behavior I have
no idea if it is a bug or not. Here is the situation:
I have a table in which o
> Umm, sorry.
Sorry again, bad day mixed with feeling rubbed the wrong way.
> and (as pointed out on this thread) it's not even valid
> for the whole of the US.
That's new on me... I have a list of U.S. Postal Codes that all consist of
two letters. This includes all the U.S. states, territories
> I ran some test to see how many queries PostGreSQL can handle per
> seconds through the libpq library : not more than 200.
>
> So, I was wondering if it is possible to write C function, compiled as
> .so which can perform SQL queries such as SELECT or UPDATE ?
>
> I think it could increase perfo
SELECT * FROM my_table WHERE my_field LIKE 'A%';
Greg
- Original Message -
From: "Jorch" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, January 16, 2001 12:39 PM
Subject: Query question
> How can I make query
>
> SELECT * FROM my_table WHERE my_field = 'A*';
>
> Meaning
Someone will correct me if I'm wrong, but I'm pretty sure all you have to do
is start a transaction. Example:
begin;
create table t1 (col1 varchar(10));
insert into t1 (col1) values ('test123');
rollback;
Greg
- Original Message -
From: Guang Mei
To: [EMAIL PROTECTED]
Sent: Thurs
> >> DROP TABLE employees
> >Error: ERROR: Relation 'employees' does not exist
> >
> >And execution halts which is I believe why he wanted to check for the
> >existence before trying to DROP. I'd love to know if this exists as
well...
> >would come in very handy during development time.
>
> E
> DROP TABLE employees
Error: ERROR: Relation 'employees' does not exist
And execution halts which is I believe why he wanted to check for the
existence before trying to DROP. I'd love to know if this exists as well...
would come in very handy during development time.
Greg
> Yeah,
>
> DROP
I had meant to post my reply to the list... I'm used to just hitting reply
and sending it directly to the list. I would've kept it at that, but since
Vince felt he had to share, I thought I'd just post this as well.
Greg
> > > Then it goes right back to what someone else eluded to... If you
don
> Then it goes right back to what someone else eluded to... If you don't
> like what you see in the documentation, contribute. Send in diffs, or
> even new items. If you don't know what's needed go to the DOCS list and
> ask. Getting people to help with documentation for ANY project is like
>
abase as detailed
in the example in the documentation (located at:
http://www.postgresql.org/docs/postgres/xplang.htm#AEN22060 ). Specifically
I executed both the CREATE FUNCTION and CREATE TRUSTED PROCEDURAL LANGUAGE
statements.
Any help would be most appreciated.
Gregory Wood
55 matches
Mail list logo
