Lou Duchez wrote:
Like everyone else, I use pg_dump for backup purposes; I have a cron job
that runs a pg_dump whose output is then FTP'd elsewhere. Two things
that would make my life easier:
1) grant select on database ... or, hypothetically, grant select on
cluster. The goal would be to
am Thu, dem 15.02.2007, um 22:13:31 +0100 mailte Bjørn T Johansen folgendes:
I have a table that I want to find rows that have the same value in two
fields, e.g. all rows that have the same date and also the
same productionid...
How do I write such an sql statement?
I'm not sure if I
* Kenneth Downs ([EMAIL PROTECTED]) wrote:
We use real database users in our systems, we don't connect in with an
over-endowed user and then arbitrate security in client code.
Therefore, we depend entirely upon the server's ability to enforce security.
We do the same thing. :)
The
I am writing a script to carry out updates from Postgres 7.2.1 to
8.1.5. The strategy is as follows:
1. Take the dump of the 7.2.1 database using pg_dump (of 8.1.5)
2. Stop the database
3. Uninstall the database
4. Install the database and start the database
5. Restore the dump taken in step 1
I have very large database that is in postgresql and in particular one table
that take up too much space. I know that in mysql one can compress
individual tables with isam-compression. I understand that postgresql
compresses text automatically, is there anyway to compress my large table
any
Ron Johnson [EMAIL PROTECTED] writes:
-BEGIN PGP SIGNED MESSAGE-
Am 15.02.2007 um 11:21 schrieb Marc Evans:
These *peeep* [deleted] compared MySQL with MyISAM to ACID compliant
databases. So why not compare an F-15 to 747? What? Apples and Oranges?
Bad analogy. Both the F-15 and
Kenneth Downs [EMAIL PROTECTED] writes:
Except for the hole. On a public site that lets users register, we have
to have way to let the web server assume the role of somebody who has
createuser privelege, and that's pretty much the end of the no-root
policy. If an exploit could be placed,
Alvaro Herrera [EMAIL PROTECTED] writes:
Casey Duncan wrote:
I'm curious how template0 got stomped on.
Heh :-) Sorry, they are all my bugs. I guess you should be throwing
stones at me or something.
The pre-8.1 theory was that template0 is (supposed to be) cleanly frozen
and hence never
Jeff Ross [EMAIL PROTECTED] writes:
This used to work before my upgrade to 8.2.1.
Which version were you using before?
The error the function now throws is:
jross%wykidsERROR: invalid regular expression: invalid backreference
number
2007-02-15 15:32:57.264729500 jross%wykidsCONTEXT: SQL
In response to Oleksandr Pryymak [EMAIL PROTECTED]:
Actually we've got a problem developing a DB with a lot of logic in it:
Our database has couple different interfaces to other systems (made
using views and procedures) and some interfaces need to be public or
semi-public. Even if we
2007/2/16, Paul Lambert [EMAIL PROTECTED]:
In setting up some functions to load data from a csv file, I'm doing the
following in psql on Weendoze:
AutoDRS=# CREATE OR REPLACE FUNCTION fnLoadAppraisals()
AutoDRS-# RETURNS void AS
AutoDRS-# $BODY$
AutoDRS$# DROP TABLE IF EXISTS
AutoDRS=# select fnLoadAppraisals();
ERROR: relation with OID 18072 does not exist
CONTEXT: SQL function fnLoadAppraisals statement 5
18072 is the OID of table appraisals_temp_load
If I run the code within the function by itself, i.e. copy and paste
the 6 lines of SQL int psql it runs
Hello,
I'm looking for some 'up-to-date' documentation about the physical
storage architecture used in postgresql, especially with respect to
vacuuming and maintenance in general. I've found some basics in the
documentation, and some interesting articles from 1987 (so I'm not sure
about it's
Tom Lane wrote:
Kenneth Downs [EMAIL PROTECTED] writes:
Except for the hole. On a public site that lets users register, we have
to have way to let the web server assume the role of somebody who has
createuser privelege, and that's pretty much the end of the no-root
policy. If an exploit
Arindam [EMAIL PROTECTED] writes:
In all the above cases, this is the error I get:
pg_restore: [archiver (db)] could not execute query: ERROR: cannot
drop schema public because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
Command was: DROP
Not exactly what I want... I don't know the date or id, I just need to find all
rows that have the same date
and the same id..
BTJ
On Thu, 15 Feb 2007 16:46:21 -0600
Ron Johnson [EMAIL PROTECTED] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/15/07 15:13, Bjørn T Johansen
On Thu, Feb 15, 2007 at 10:04:44PM -0800, vanessa wrote:
I have very large database that is in postgresql and in particular one
table that take up too much space.
How are you determining what too much space is? How are you
measuring how much space the table takes up, how much space are you
Hello Tom,
thanks for your answer.
But I don't understand why there are changes of the databases template1 and
template0 at all?
I thought they are only templates.
Regards
Michaela
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: MG [EMAIL PROTECTED]
Cc:
I was trying to create a rule to set a column to false whenever another
column was changed:
CREATE RULE...
ON UPDATE TO criterion
WHERE new.pattern::text old.pattern::text DO UPDATE table SET flag =
false
WHERE id = old.id
pattern | id
12345 | 1
= update criterion set pattern = '12345'
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/16/07 01:44, Bjørn T Johansen wrote:
Not exactly what I want... I don't know the date or id, I just
need to find all rows that have the same date and the same id..
SELECT SOME_DATE, PRODUCTIONID, COUNT(*)
FROM A_TABLE
GROUP BY SOME_DATE,
Or, if you need the whole row:
SELECT at1.* FROM a_table as at1
WHERE EXISTS (
SELECT 1 FROM a_table as at2
WHERE at2.my_date = at1.my_date
AND at2.prod_id = at1.prod_id
AND at2.primary_key at1.primary_key
)
This form can easily be adjusted to show
only certain duplicates, or only to
Gene [EMAIL PROTECTED] writes:
I'm not sure why it's detecting recursion in this case when the rule
conditional should be false
Rules are macros, which means that expansion has to terminate
statically, not dynamically. For the particular purpose you seem to
have here, it'd be a lot more
Gene [EMAIL PROTECTED] schrieb:
I was trying to create a rule to set a column to false whenever another column
was changed:
CREATE RULE...
ON UPDATE TO criterion
WHERE new.pattern::text old.pattern::text DO UPDATE table SET flag =
false
WHERE id = _o_l_d_._i_d
pattern | id
Gene wrote:
I was trying to create a rule to set a column to false whenever another
column was changed:
Don't. Use a BEFORE trigger, and instead of issuing a new UPDATE, just
change the NEW record that you return. It's conceptually much simpler.
--
Alvaro Herrera
Lou Duchez wrote:
Lou Duchez wrote:
Like everyone else, I use pg_dump for backup purposes; I have a cron job
that runs a pg_dump whose output is then FTP'd elsewhere. Two things
that would make my life easier:
1) grant select on database ... or, hypothetically, grant select on
cluster.
Nico Grubert wrote:
/usr/local/lib/python2.4/config/libpython2.4.a(abstract.o): relocation
R_X86_64_32 against `a local symbol' can not be used when making a
shared object; recompile with -fPIC
/usr/local/lib/python2.4/config/libpython2.4.a: could not read
symbols:
Bad value
Any idea,
Certainly, I've
tried grant select on database mydatabase to user myuser; it doesn't
work, because select is not a database-level privilege.
Sorry, you're right on that one. I misread it. However, it shouldn't
be too hard to write a script, either in a procedural language or higher
On Feb 15, 2007, at 5:50 PM, Alvaro Herrera wrote:
Casey Duncan wrote:
On Feb 15, 2007, at 5:21 PM, Alvaro Herrera wrote:
Casey Duncan wrote:
To fix the problem, set pg_database.datallowconn=true for template0,
then connect to it and do a VACUUM FREEZE. Then set
datallowconn=false
Hi from Spain,
I have a problem with TSearch2,
I have a table with more than a million registers (a table of books, for
example),
I made a tsearch2 index for one of my fields (the title of the books, for
example),
I make queries from that table, over the tsearch2 index. Then some of my
Hi list,
I am looking for this guy for some help with Slony-I.
http://people.planetpostgresql.org/xzilla/index.php?/archives/200-Alpha-
testing-Slony-on-win32-Crib-Notes.html
Robert
Casey Duncan wrote:
On Feb 15, 2007, at 5:50 PM, Alvaro Herrera wrote:
Hum, yeah, I forgot to mention that you need to create the 098E
pg_clog
segment for that to work at all :-) Fill it with byte 0x55 till the
needed position, which is the bit pattern for all transactions
committed.
On 2/16/07, Rafa Comino [EMAIL PROTECTED] wrote:
Hi from Spain,
I have a problem with TSearch2,
I have a table with more than a million registers (a table of books, for
example),
I made a tsearch2 index for one of my fields (the title of the books, for
example),
I make queries from that
On 2/16/07, Lou Duchez [EMAIL PROTECTED] wrote:
Like everyone else, I use pg_dump for backup purposes; I have a cron job
that runs a pg_dump whose output is then FTP'd elsewhere. Two things
that would make my life easier:
1) grant select on database ... or, hypothetically, grant select on
I created a function with VOLATILE directive. it's
body looks like shown bellow
cut start
begin
insert into monitor(ts, c1) values(LOCALTIMESTAMP,
'Step 1000'); -- start time
-- query below runs for 20min
insert ito t1 select * from big_table
-- this timestamp
Vladimir Zelinski wrote:
I don't believe that it's bug, probably it's a feature
of the postgreSql database.
Correct.
Is any way to insert a timestamp within the same
transaction that would have current system time (not
time of the beginning of the transaction)?
timeofday()
--
Alvaro
On Feb 16, 2007, at 12:46 PM, Ezequias Rodrigues da Rocha wrote:
Hi list,
I am looking for this guy for some help with Slony-I.
Then why don't you send Robert a direct email? He's not that hard to
find with google.
Or perhaps ask your question here; there are lots of smart folks
Thank you very much.
It works.
Vladimir
--- Alvaro Herrera [EMAIL PROTECTED] wrote:
Vladimir Zelinski wrote:
I don't believe that it's bug, probably it's a
feature
of the postgreSql database.
Correct.
Is any way to insert a timestamp within the same
transaction that would have
Scott Ribe wrote:
Could the below message be modified:
The database cluster was initialized with PG_CONTROL_VERSION 906166272, but
the server was compiled with PG_CONTROL_VERSION 822.
By also showing the version numbers in hex, like so:
The database cluster was initialized with
On 2/11/2007 1:02 PM, Benjamin Arai wrote:
Hi Magnus,
Think this can be avoided as long the the queries executed on the lower
priority process never lock anything important. In my case, I would
alway be doing inserts with the lower priority process, so inversion
should never occur. On the
Hi Jan,
That makes sense. Does that mean that a low-priority road-block can
cause a deadlock or just an very long one lock?
Benjamin
Jan Wieck wrote:
On 2/11/2007 1:02 PM, Benjamin Arai wrote:
Hi Magnus,
Think this can be avoided as long the the queries executed on the
lower priority
In response to Bruce Momjian [EMAIL PROTECTED]:
Scott Ribe wrote:
Could the below message be modified:
The database cluster was initialized with PG_CONTROL_VERSION 906166272, but
the server was compiled with PG_CONTROL_VERSION 822.
By also showing the version numbers in hex, like
On 2/16/2007 4:56 PM, Benjamin Arai wrote:
Hi Jan,
That makes sense. Does that mean that a low-priority road-block can
cause a deadlock or just an very long one lock?
It doesn't cause any deadlock by itself. Although the longer one holds
one lock, before attempting to acquire another, the
Bill Moran wrote:
In response to Bruce Momjian [EMAIL PROTECTED]:
Scott Ribe wrote:
Could the below message be modified:
The database cluster was initialized with PG_CONTROL_VERSION 906166272,
but
the server was compiled with PG_CONTROL_VERSION 822.
By also showing the
Fair enough, thanks for the clarification.
Benjamin
Jan Wieck wrote:
On 2/16/2007 4:56 PM, Benjamin Arai wrote:
Hi Jan,
That makes sense. Does that mean that a low-priority road-block
can cause a deadlock or just an very long one lock?
It doesn't cause any deadlock by itself. Although
Tomas Vondra wrote:
AutoDRS=# select fnLoadAppraisals();
ERROR: relation with OID 18072 does not exist
CONTEXT: SQL function fnLoadAppraisals statement 5
18072 is the OID of table appraisals_temp_load
If I run the code within the function by itself, i.e. copy and paste
the 6 lines of SQL
On 2/16/2007 5:05 PM, Benjamin Arai wrote:
Fair enough, thanks for the clarification.
What you can do to throttle things in a reasonable manner would require
that your application knows which transaction requires updating when it
begins it. If that is the case, you can setup multiple
Hi Jan,
That is true but it really only solves the case for readers/writers.
In the long run I feel there should be some mechanism to determine the
priority of a query either on a user or query basis. This would lend
PostgreSQL to a whole new industry that is currently only filled with
On Friday 16. February 2007 07:10, Tom Lane wrote:
Perhaps this
paper can be described as comparing an F-15 to a 747 on the basis of
required runway length.
There ought to be a proper name for this kind of pseudo-technical Gonzo
journalism. The Internet is full of it.
--
Leif Biberg
If I am reading the (7.4) docs correctly, privileges can be granted
only with respect to tables that exist at the time the GRANT command
is given - there is no
GRANT ALL ON * TO PUBLIC
or some such that would result in subsequently created tables having
public privileges.
Is this so?
[EMAIL PROTECTED] (Vladimir Zelinski) writes:
I tried function now(),current_timestamp() but all of
them behave similar.
I don't believe that it's bug, probably it's a feature
of the postgreSql database.
Indeed, that is intentional. CURRENT_TIMESTAMP and NOW() return the
time at which the
The problem with gettimeofday() is that it returns a string, rather than
a timestamp. This was all clarified in 8.2:
Add clock_timestamp(), statement_timestamp(), and
transaction_timestamp() (Bruce)
clock_timestamp() is the current wall-clock time,
John D. Burger wrote:
If I am reading the (7.4) docs correctly, privileges can be granted
only with respect to tables that exist at the time the GRANT command
is given - there is no
GRANT ALL ON * TO PUBLIC
or some such that would result in subsequently created tables having
Hi,
I have a table with four text fields on two different machines
that contain information about mail list passwords/software keys
and similar information. Neither table has any keys or constraints.
I do have dbi installed on each machine...
Is there an elegant way I can merge/update the two
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/16/07 17:25, Chris Browne wrote:
[EMAIL PROTECTED] (Vladimir Zelinski) writes:
I tried function now(),current_timestamp() but all of
them behave similar.
I don't believe that it's bug, probably it's a feature
of the postgreSql database.
Ashish Karalkar wrote:
Hello All,
I want to prompt user to input some value and do some action on that value in
runtime of a sql script.
Is there any psql command to do this ??
I can use \echo do display massage
but to take input what is the command?
Thanks in advance
You can do:
Magnus Hagander wrote:
Most likely, you do not want to do this. You *can* do it, but you are
quite likely to suffer from priority inversion
Papers I've read suggest that the benefits of priorities
vastly outweigh the penalties of priority inversion for
virtually all workloads on most all
It has come to the attention of the core team of the PostgreSQL project
that insecure programming practice is widespread in SECURITY DEFINER
functions. Many of these functions are exploitable in that they allow
users that have the privilege to execute such a function to execute
arbitrary
This may be bad design on my part, but...
I have three tables of interest...Account, Employee, and
AccountEmployeeRelation. There is a many-to-many relationship between
accounts and employees. The join table also contains a column
indicating what role the employee plays on this account.
Hi all,
I'm just experimenting with tsearch2 - I have it all working fine
but I was wondering if there's a way to create indexes containing
vector columns from multiple tables? Or if not, how do people
usually manage this kind of issue?
Toby
---
I'm not sure I understand completely, but here's one idea.
in the backend, when the user submits their changes:
1) Pull the current state of AccountEmployeeRelation for the
account you're working on
2) Compare the current state to what the user posted, and
determine what needs to be added and
60 matches
Mail list logo
