It looks like the indexes are in fact used when trigger variables are
referenced in where clauses in PL/pgSQL. Thanks for the idea of
investigating the plans in the logs for this info.
Follow-up question: are indexes used in dynamically executed queries?
Rather than SEQSCAN or INDEXSCAN in the D
Greg Stark wrote:
I understand that, I just can't see why an index lookup can't be used on "per
row" basis.
Well, how would that work?
Well, good point, the "per row" is a set of data selected as a product
of the "static" part os the query (non volatile parts), the only thing
you can do w
Florian G. Pflug wrote:
Because the _whole_ _point_ of an index is to find matching rows
_without_ scanning the whole table. IF you have to look at every row
anyway, then just might as well to an sequential scan.
I am sorry it took me this long to understand this, but I think I got it
now thanks
Michael Fuhr wrote:
On Tue, Jan 18, 2005 at 07:34:59PM -0800, Adrian Klaver wrote:
Actually universal newline support seems to be covered by the following PEP
and is present in the version of Python(2.3) I am running.
http://www.python.org/peps/pep-0278.txt
I see the following in the PEP:
Ther
Michael,
See "Object Identifier Types" in the "Data Types" chapter.
SELECT * FROM pg_attribute WHERE attrelid = 'tablename'::regclass;
Thanks. That worked like a champ!
Ken
---(end of broadcast)---
TIP 6: Have you searched our list archives?
On Tue, Jan 18, 2005 at 11:34:41PM -0600, Mike G. wrote:
> Isn't the syntax CREATE OR REPLACE FUNCTION chargeratetest() RETURNS
> "trigger" AS'
That's the same thing as
> > CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS'
with different identifiers quoted. See the documenta
[Please don't post in HTML.]
On Wed, Jan 19, 2005 at 04:45:14PM +1100, Jamie Deppeler wrote:
> What i am trying to do is to update a field based on a sql query
> set notes='hello' is just being used as a test but i can not seem
> to make this simple update work
Do you want to modify a column in
On Wed, Jan 19, 2005 at 12:14:57AM -0500, Ken Tozier wrote:
> I recently stumbled upon the system catalog functions here
> "http://www.postgresql.org/docs/7.4/static/catalogs.html"; and see that
> it's easy to get a list of all databases and relatively easy to get a
> list of tables, but there
What i am trying to do is to update a field based on a sql query
set notes='hello' is just being used as a test but i can not seem to make this simple update work
Michael Fuhr wrote:
On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote:
Think im doing something wrong
Isn't the syntax CREATE OR REPLACE FUNCTION chargeratetest() RETURNS "trigger"
AS'
?
On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote:
> Hi,
>
> Think im doing something wrong here, cant seem to resolve the problem i
> have a trigger which is calling a update function and when
Look at the SQL in the PG Admin III
project source code base.
http://www.pgadmin.org/
It’s non-trivial SQL to collect all
the information about a table.
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of Ken Tozier
Sent: Tuesday, January 18, 2005
9:15 PM
To: Pos
On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote:
> Think im doing something wrong here, cant seem to resolve the problem i
> have a trigger which is calling a update function and when it gets to a
> update it goes into a infinite loop
recursion, noun. See recursion.
> CREATE TR
I recently stumbled upon the system catalog functions here "http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that it's easy to get a list of all databases and relatively easy to get a list of tables, but there doesn't seem to be any built in method for retrieving a table definition.
On Tue, Jan 18, 2005 at 10:53:40AM -0600, Thomas F.O'Connell wrote:
> UPDATE mytable SET mybigintcol = somevalue WHERE mybigintcol =
> NEW.myotherbigintcol;
>
> This shows up in the logs with the NEW variable converted to unquoted
> constant data. Is PL/PgSQL smart enough to help the planner fi
Michael Fuhr <[EMAIL PROTECTED]> writes:
> If the catalog versions match then you shouldn't need to do an
> initdb; if they're different then you will. Making a full dump
> anyway before upgrading might be prudent just in case, however.
> The most recent catalog version change appears to have bee
Hi,
Think im doing something wrong here, cant seem to resolve the problem i
have a trigger which is calling a update function and when it gets to a
update it goes into a infinite loop
code
Trigger
CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE
ON "chargeratetest" FOR EACH ROW
EXECUTE PROCED
On Tue, Jan 18, 2005 at 11:03:22PM -0300, Alvaro Herrera wrote:
> On Tue, Jan 18, 2005 at 07:33:51PM -0600, Jim C. Nasby wrote:
> > On Wed, Jan 19, 2005 at 02:15:42AM +0100, Florian G. Pflug wrote:
> > > You can, howevery, accelerate something like "where f in (1,2,3,4)". You
> > > just scan the i
You do not have to send the transaction all at once.
Paul Tillotson
A question about using transactions from php:
Does the entire transaction have to be sent all at once,
or can I begin the transaction, issue commands one
at a time, and then end the transaction?
---(end
On Tue, Jan 18, 2005 at 07:34:59PM -0800, Adrian Klaver wrote:
> Actually universal newline support seems to be covered by the following PEP
> and is present in the version of Python(2.3) I am running.
> http://www.python.org/peps/pep-0278.txt
I see the following in the PEP:
There is no suppo
You can issue them one at a time - however you can't have a transaction
that spans multiple pages (ie you can't start it on index.php and finish
it on end.php).
Rick Schumeyer wrote:
A question about using transactions from php:
Does the entire transaction have to be sent all at once,
or can
Actually universal newline support seems to be covered by the following PEP
and is present in the version of Python(2.3) I am running.
http://www.python.org/peps/pep-0278.txt
I would tend to agree with Hong Yuan that the problem exists in plpythonu's
handling of newlines.
On Tuesday 18 January 2
On Tue, Jan 18, 2005 at 04:21:51PM -0500, Eric E wrote:
>I'm tearing my hair out trying to solve the following problem. I
> want to be able to retrieve the value of the primary key in a trigger
> function. I have tried using PL/PGSQL and PL/PERL, but a PL/PERL
> trigger function crashes t
A question about using transactions from php:
Does the entire transaction have to be sent all at once,
or can I begin the transaction, issue commands one
at a time, and then end the transaction?
On Tue, Jan 18, 2005 at 07:33:51PM -0600, Jim C. Nasby wrote:
> On Wed, Jan 19, 2005 at 02:15:42AM +0100, Florian G. Pflug wrote:
> > You can, howevery, accelerate something like "where f in (1,2,3,4)". You
> > just scan the index 4 times, each time for a different value. Of course,
> > if the num
On Tue, Jan 18, 2005 at 04:36:21PM -0800, Ron Mayer wrote:
> Subject says it all.
You can check if an upgrade requires an initdb by comparing catalog
versions between your current version and the new version. To find
out your current catalog version, run pg_controldata and look for
the following
On Wed, Jan 19, 2005 at 02:15:42AM +0100, Florian G. Pflug wrote:
> You can, howevery, accelerate something like "where f in (1,2,3,4)". You
> just scan the index 4 times, each time for a different value. Of course,
> if the number of values becomes larger and larger, there is a point
> where it'
Bo Lorentsen wrote:
Greg Stark wrote:
If Postgres used an index it would call odd(), which would return 1
because
it's the first time, and then Postgres would go look up the rows where
col is
1 and return all of them. That's a very different behaviour from if
the index
isn't used. If all the rec
Subject says it all. I have a couple 8.0.0rc3 databases (about
1/2TB of GIS data and am upgrading to 8.0.0. Note that I'll
dump&restore when PostGIS gets out of the release-candidate
cycle. If I don't need an initdb I'll probably go to 8.0.0
now. If I do need one I'd try keep running 8.0.0rc3
I had a problem in the past with the vaccumdb command on 7.2.3
mysteriously never returning when run from cron on FreeBSD, but it ran
fine from cmdline. Had to be an environmental issue. Not having time to
track it down, I tried an alternative lazy method...
/usr/local/bin/vacuumdb -z -f dbname
On Tue, Jan 18, 2005 at 04:43:30PM -0500, Tom Lane wrote:
> Joseph Shraibman writes:
> > Has this error from 7.4.6 been fixed in 8.0? ERROR: Unicode
> > characters greater than or equal to 0x1 are not supported
>
> No. There was some code submitted for it, but too late in the 8.0
> dev cycl
On Tue, 18 Jan 2005 22:31:43 +, Adam Witney <[EMAIL PROTECTED]> wrote:
> On 18/1/05 8:38 pm, "Lonni J Friedman" <[EMAIL PROTECTED]> wrote:
>
> > On Tue, 18 Jan 2005 18:23:23 +, Adam Witney <[EMAIL PROTECTED]> wrote:
> >>
> >> Hi,
> >>
> >> I am setting up the backup strategy for my databas
On 18/1/05 8:38 pm, "Lonni J Friedman" <[EMAIL PROTECTED]> wrote:
> On Tue, 18 Jan 2005 18:23:23 +, Adam Witney <[EMAIL PROTECTED]> wrote:
>>
>> Hi,
>>
>> I am setting up the backup strategy for my database.
>>
>> The database contains around 25 tables containing quite a lot of data that
>>
Many thanks for the help Michael
I did restart the postmaster a few times.
I will give the other suggestions a go and let you know what happens.
David
From: Michael Fuhr <[EMAIL PROTECTED]>
To: David Klugmann <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem getti
Joseph Shraibman writes:
> Has this error from 7.4.6 been fixed in 8.0?
> ERROR: Unicode characters greater than or equal to 0x1 are not supported
No. There was some code submitted for it, but too late in the 8.0
dev cycle...
regards, tom lane
--
Hi all,
I'm tearing my hair out trying to solve the following problem. I
want to be able to retrieve the value of the primary key in a trigger
function. I have tried using PL/PGSQL and PL/PERL, but a PL/PERL
trigger function
crashes the postgress process.
I am able to use TG_RELNAME and dbl
On Tue, Jan 18, 2005 at 01:16:55PM -0500, Christopher Browne wrote:
> It would be kind of neat if there were some table into which
> transactions were logged; in that case, there would presumably be a
> couple of timestamps, namely the time at which the transaction began
> and the time at which th
On Tue, 18 Jan 2005 18:23:23 +, Adam Witney <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I am setting up the backup strategy for my database.
>
> The database contains around 25 tables containing quite a lot of data that
> does not change very much (and when it does it is changed by me). And aroun
Has this error from 7.4.6 been fixed in 8.0?
ERROR: Unicode characters greater than or equal to 0x1 are not supported
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
On Mon, 2005-01-17 at 17:52 -0500, Tom Lane wrote:
> John DeSoi <[EMAIL PROTECTED]> writes:
> Presumably you are using a fairly vanilla set of configure options, too.
> Matthew's missing symbols are Kerberos subroutines, so evidently his
> problem is that he configured --with-krb5 but didn't bother
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Antony
Paul) transmitted:
> I read about xmin in a thread on optimistic concurrency control.
> Can you guys tell me how to interpret this value. I want to convert
> this to date format. Is it possible ?
xmin is a transac
I'm using DBD::Pg under mod perl, and I'm trying to find a way to
workaround the fact that DBD::Pg fetches all the data at once in a
query and sticks it into memory before you execute a fetch. Right now
I'm having to kill the apache child after it's finished running in
certain cases, and I'd rathe
On Tue, 18 Jan 2005, Vladimir S. Petukhov wrote:
Hi!
Sorry for my English..
I want to dinamcly change type of column. If possible, of course.Trivial idia -
create new temporary column, try to write value from old columt to temporarity
(if type conersion ok - this made using select/update command
Hi,
I am setting up the backup strategy for my database.
The database contains around 25 tables containing quite a lot of data that
does not change very much (and when it does it is changed by me). And around
20 tables containing data which will be created and updated by the users
regularly.
I
Michael Meskes wrote the following on 01.01.2005 16:08:
On Tue, Dec 28, 2004 at 10:16:04PM -, John Smith wrote:
I'm trying to convert a series of C programs written originally using
Informix ESQL to use Postgres' ECPG.
...
575 EXEC SQL connect to pdev_changename;
...
I'm using Postg
On Tue, Jan 18, 2005 at 03:05:34PM +, David Klugmann wrote:
>
> I have the following entries in my postgresql.conf file yet it doesn't seem
> to log anything.
Did you restart the backend after modifying postgresql.conf?
> syslog_facility = 'LOCAL0'
Have you configured /etc/syslog.conf to s
I've got a question about how PL/PgSQL passes things to the planner
based on statement logging.
E.g., I have a statement like this in PL/PgSQL:
UPDATE mytable SET mybigintcol = somevalue WHERE mybigintcol =
NEW.myotherbigintcol;
This shows up in the logs with the NEW variable converted to unquo
On Tue, Jan 18, 2005 at 07:01:45PM +, Vladimir S. Petukhov wrote:
> I want to dinamcly change type of column. If possible, of course.
The FAQ discusses this:
http://www.postgresql.org/files/documentation/faqs/FAQ.html#4.4
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---
I think that INFO gives you information about your current usage and
that DETAIL tells you what is currently set in your configuration.
In this example, the default settings appear to be sufficient for your
database. If the values in INFO were larger than the values in DETAIL,
you would want to
Hi!
Sorry for my English..
I want to dinamcly change type of column. If possible, of course.
Trivial idia - create new temporary column, try to write value from old columt
to temporarity (if type conersion ok - this made using select/update command
and type conversion checks on client's side),
The problem may be in your /etc/syslog.conf file. It directs different
classes of messages different places, and can direct them to another
machine.
Rick
Cuando subo la base de datos con pg_restore -d mydb < mydb.tar restablece toda la informacion pero los acentos y ñ muestran caracteres en otra codificacion como puedo solucionar esto__Correo Yahoo!Espacio para todos tus mensajes, antivirus y antispam
On Tue, Jan 18, 2005 at 02:43:09PM +0100, Karsten Hilbert wrote:
> > Can you guys tell me how to interpret this value. I want to convert
> > this to date format. Is it possible ?
> No.
>
> XMIN holds the id of the transaction which did the most recent
> change to a row as is visible from within th
Hi,
Is there an official way to cite PostgreSQL in scientific publications?
This might sound somewhat strange to some of you, but e.g. the R project
(http://www.r-project.org, open source environment for statistical
computing as a side note) has a FAQ entry like this:
2.8 Citing R
To cite R in
Hi
I am trying to log my sql statements to the syslog file.
I have the following entries in my postgresql.conf file yet it doesn't seem
to log anything.
The OS is Solaris 9
I am looking at /var/adm/messages and can't see anything from Postgres and I
have run lots of sql.
I saw one article on th
On Tue, Jan 18, 2005 at 02:43:09PM +0100, Karsten Hilbert wrote:
> XMIN holds the id of the transaction which did the most recent
> change to a row as is visible from within the current
> transaction. Unless it wraps around (is that possible ?) older
> transactions should have XMIN values lower th
On Tue, 18 Jan 2005 11:12:34 +0100, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> At office we have a Win2k LAN to which my freebsd postgresql server box
> is connected via Samba. On this box I have a script, called 'crono', which
> is dealt by /usr/bin/cron every working day's night at 1 am. As
On Mon, 17 Jan 2005 23:44:37 -0500, Madison Kelly <[EMAIL PROTECTED]> wrote:
> Tom Lane wrote:
> > Michael Fuhr <[EMAIL PROTECTED]> writes:
> >
> >>On Mon, Jan 17, 2005 at 09:03:17PM -0500, Madison Kelly wrote:
> >>
> >>>Is there any way I can log and/or display database calls for a
> >>>specific d
> Can you guys tell me how to interpret this value. I want to convert
> this to date format. Is it possible ?
No.
XMIN holds the id of the transaction which did the most recent
change to a row as is visible from within the current
transaction. Unless it wraps around (is that possible ?) older
tran
Michael Fuhr <[EMAIL PROTECTED]> writes:
> http://docs.python.org/ref/physical.html
> "A physical line ends in whatever the current platform's convention
> is for terminating lines. On Unix, this is the ASCII LF (linefeed)
> character. On Windows, it is the ASCII sequence CR LF (return
> followe
Hi,
I read about xmin in a thread on optimistic concurrency control.
Can you guys tell me how to interpret this value. I want to convert
this to date format. Is it possible ?
rgds
Antony Paul
---(end of broadcast)---
TIP 5: Have you checked our
David Teran wrote:
Hi,
maybe its a silly question, but is there a way to list all databases
with an SQL command? I know about the \l command from psql, but i wonder
if such things are possible with SQL.
Look into the pg_class table or the information schema.
Also, if you start psql with -E then y
Am Dienstag, 18. Januar 2005 11:32 schrieb David Teran:
> maybe its a silly question, but is there a way to list all databases
> with an SQL command? I know about the \l command from psql, but i
> wonder if such things are possible with SQL.
SELECT * FROM pg_database;
--
Peter Eisentraut
http://
Hello,
I'd like to hear what you think about the following -
We have an application (database plus software) that is used as a central
application. Sub-applications can connect to it and perform certain actions
like reading and writing data. The central application has some objects
which the sub-
Hi,
maybe its a silly question, but is there a way to list all databases
with an SQL command? I know about the \l command from psql, but i
wonder if such things are possible with SQL.
regards, David
---(end of broadcast)---
TIP 9: the planner will
At office we have a Win2k LAN to which my freebsd postgresql server box
is connected via Samba. On this box I have a script, called 'crono', which
is dealt by /usr/bin/cron every working day's night at 1 am. As you can
see below this crono script vacuums all the DBs and dumps mydb to a samba
shar
The system administrator will need to be able to see it, and will need
remote
access to it.
The security requirements are extremely high, the standard in this case
is set
by the state, so encryption will be a must.
I hope you remark that these two are incompatible.
* For instance 'extreme
Hi,
Looks to me like you have to add -lkrb5 and -lssl to your compile flags
as well. Perhaps some more others (you can use nm in conjunction with
grep to find which libraries define the missing symbols (On OpenBSD
like so: 'cd /usr/lib && nm -o * | grep krb5_free_context' but that
might differ on
ok.
Thanks all for you help.
I will take a look to 8.0
Laurent Marzullo
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Michael Fuhr
Sent: Monday, January 17, 2005 5:59 PM
To: Laurent Marzullo
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PQexecPa
The system administrator will need to be able to see it, and will need remote
access to it.
The security requirements are extremely high, the standard in this case is set
by the state, so encryption will be a must.
the table itself will need to be accessed by triggers, so the encryption plus
the
On Tue, Jan 18, 2005 at 01:24:31AM -0500, Tom Lane wrote:
>
> It seems odd that in today's climate the Python interpreter
> would not cope well with Windows-style newlines. Maybe there is some
> configuration issue with Python itself?
http://docs.python.org/ref/physical.html
"A physical line en
70 matches
Mail list logo
