Hi all,
If I try to execute a dynamic query inside a function with a group by
statement, returning a setof, I get a weird error. It may be due to
the antiquated database version, but I would appreciate all the info I
can get (I tried looking in the PG bug tracker, but ... hahaha). If
it is as
Hi all,
(Version 3.5.5)
I have tried to figure this out, but the docs, google, and my all
imagination fail me. I want to use a join clause with a using list
in an update statement. The following works, but it uses the WHERE
version of a join:
update new_pivoted_table a set 2008-11-10 =
Hi all,
Does anyone have any advice on using application code with a
refcursor? This is a follow up to my is there a safe-ish way to
execute arbitrary sql ? Now that I have way to execute my arbitrary
sql, I need to generate an html table with the arbitrary result inside
some PHP (or whatever),
Hi all
Is there a away to set up a schema such that a certain role has (1)
read only access to (2) all the tables, but (3) must use predefined
functions to use that access?
Items 1 and 2 are so that the end user doesn't stomp on the data.
I want item 3 in order to force the application
you can set transactions read only but the user can turn them off. Use
views, functions and GRANT.
Views was the key word. I had tried to do it with functions and GRANT alone.
Thanks to the collective brain that is a listserv.
-W
--
Sent via pgsql-general mailing list
Hi all,
I am writing an application that allows users to analyze demographic
and economic data, and I would like the users to be able to pick
columns, transform columns with functions (economists take the
logarithm of everything), and write customized WHERE and GROUP-BY
clauses. This is kind of
If they're that smart, they're smart enough to deal with SQL, and
likely to be frustrated by a like-sql-but-not command language or
a GUI query designer.
Instead, create a user that only has enough access to read data (and
maybe create temporary tables) and use that user to give them
a sql
Or do it with simple combo boxes if you
want to limit the users to crippled queries.)
I want to limit my users to *half* crippled queries -- arbitrary
column lists, where clauses, group by lists, and sort by lists. I
want to make sure that they aren't doing any data modifications nested
inside
Am I right to avoid to VIEWS within application code?
How one uses views is more a matter of taste and best practices, than
a matter of rules like this. Frankly, this rule sounds rather ill
conceived.
My feeling is that views can be difficult to maintain when they are
nested, but otherwise use
Untested ideas (beware):
Use an insert trigger that:
curr_seq := select max(seq) from foo where field_id = NEW.field_id
if curr_seq is null then NEW.seq := 0
else NEW.seq := curr_seq + 1
(You have to figure out how to build the trigger infrastructure...)
If you need to do it on a
hi
I have one of those master-detail relationships here and I need to be able
to delete the master but leave the details untouched
when you create the table with an FK constraint, use the ON DELETE SET
NULL option, or SET DEFAULT. And read the docs on CREATE TABLE:
I meant, I did not know such facility exists
When you use pgautodoc, it automatically grabs those comments and puts
them in the web page it crreates... more coolness!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
Hi all,
I have the following function:
create function new_student (text) returns text as $$
declare
wtf integer := 1;
begin
execute 'create schema ' || $1;
execute 'create role ' || $1 || 'LOGIN';
execute 'revoke all on schema public from
Hi all,
I have the following function:
create function new_student (text) returns text as $$
declare
wtf integer := 1;
begin
execute 'create schema ' || $1;
execute 'create role ' || $1 || 'LOGIN';
execute 'revoke all on schema public from
I have the following function: SNIP
Now that I know how to write the function, my design flaws and lack of
understanding are more apparent...
... I was trying to give all logged in users read-only access to the
public schema, and full access to the schema that corresponds to their
username.
On Fri, Mar 14, 2008 at 12:55 PM, Tom Lane [EMAIL PROTECTED] wrote:
Webb Sprague [EMAIL PROTECTED] writes:
Also, I revoked what I thought was everything possible on the public
schema, but a user is still able to create a table in that schema --
could someone explain:
oregon=# revoke
Start with
revoke all on schema public from public
and then grant only what you want.
Oh -- to grant select permissions on all the tables in the public
schema, do I have to do it table-by-table? I know I can write a loop
an use information_schema if necessary, but if I don't
On Fri, Mar 14, 2008 at 1:30 PM, Erik Jones [EMAIL PROTECTED] wrote:
On Mar 14, 2008, at 3:22 PM, Webb Sprague wrote:
Start with
revoke all on schema public from public
and then grant only what you want.
Oh -- to grant select permissions on all the tables in the public
Thanks to Eric and Tom, I think I have got it. Here is the function
for adding a new student, who can select anything in public and can do
anything at all in their own schema.
revoke all on schema public from public; -- done only once
create or replace function new_student (text) returns void
On Mon, Mar 10, 2008 at 11:00 AM, Justin [EMAIL PROTECTED] wrote:
That comment was not meant to be an insult or disparaging in any way what
so ever. If it was taken as such then i'm sorry.
I am sure it would have been fine in person, I just think over email
it sounded abrasive.
But could
I haven't tested but this is what I would do (uses arrays, which are
handy when you need them), with the names changed to protect the
innocent:
begin;
-- create a table with some duplicates in one of the columns (y is ck);
wsprague=# select x, x%4 as y into temp fbar from generate_series(1,10) as
It occurs to me that it shouldn't be terribly difficult to make an
alternate version of crosstab() that returns an array rather than
tuples (back when crosstab() was first written, Postgres didn't
support NULL array elements). Is this worth considering for 8.4?
How about
Post the table, the query, and the explain output, and then we can help you.
On Feb 19, 2008 7:38 PM, hewei [EMAIL PROTECTED] wrote:
Hi,Every body;
I have a table contains 100,000 rows, and has a primary key(int).
Now ,I need to execute sql command like update .. where id=*(id
is
I'm quite proud, this is my first C extension function ;-)
I'd gladly post the code if it's ok for the list users. It's more or
less 100 lines of code. This approach seems promising...
I would definitely like to see it.
By the way, Webb: I took a look at GSL and it seems to me that, from a
On Feb 1, 2008 2:31 AM, Enrico Sirola [EMAIL PROTECTED] wrote:
Hello,
I'd like to perform linear algebra operations on float4/8 arrays.
These tasks are tipically carried on using ad hoc optimized libraries
(e.g. BLAS).
If there were a coherently designed, simple, and fast LAPACK/ MATLAB
style
] wrote:
Webb Sprague wrote:
On Feb 1, 2008 2:31 AM, Enrico Sirola [EMAIL PROTECTED] wrote:
I'd like to perform linear algebra operations on float4/8 arrays...
If there were a coherently designed, simple, and fast LAPACK/ MATLAB
style library and set of datatypes for matrices and vectors
...linear algebra ...
... matrices and vectors .
...Especially if some GIST or similar index could efficiently search
for vectors close to other vectors...
Hmm. If I get some more interest on this list (I need just one LAPACK
/ BLAS hacker...), I will apply for a pgFoundry project
On Feb 1, 2008 2:31 AM, Enrico Sirola [EMAIL PROTECTED] wrote:
Hello,
I'd like to perform linear algebra operations on float4/8 arrays
Having avoided a bunch of real work wondering about linear algebra and
PG, did you consider the Gnu Scientific Library ? We would still need
to hook everything
(look at pg_dump options).
Regards,
Blazej
2008/1/16, Webb Sprague [EMAIL PROTECTED]:
In another thread, someone mentioned writing hooks for Subversion that
would grab function definitions and DDL statements from the current
database and push them into the repository?
Does anyone have
In another thread, someone mentioned writing hooks for Subversion that
would grab function definitions and DDL statements from the current
database and push them into the repository?
Does anyone have a few scripts/ cookbook examples for this? Is there
a cookbook section on the postgres wiki
I think select pg_backend_pid(); will do that.
Perfect. I tried googling but I didn't try \df *pid* which would have found it
I tried to figure out the pg_stat_activity, but I can't think of a
WHERE condition that would make it give me the info I wanted.
Thx again to everyone.
Hi all,
Is there a way to determine the pid of a database connection from
within that connection?
As a hypothetical example, I would like to be able to do the following:
$ps x
PID TTY STAT TIME COMMAND
11674 ?S 0:00 sshd: [EMAIL PROTECTED]/1
11675 pts/1Ss 0:00
Hi list,
First, my select version() gives:
PostgreSQL 8.2.4 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)
The Problem: when I run
ALTER TABLE currentsessions ADD CONSTRAINT
currentsessions_username_fkey FOREIGN KEY (username) REFERENCES
... currentsessions_username_fkey FOREIGN KEY (username)
REFERENCES authorizedusers(username) ON UPDATE CASCADE
Hmm, NO ACTION is the default.
Oh, how embarrassing. Never mind...
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company
... to determine the field datatype (text, numeric,
bool, etc.)
I am not sure if this helps, but you can dig around in the system
files (pg_catalog.*), and probably write a query that gets the types
of every column in the data table you want to insert to.
Not a big push, but maybe it will get
Is it
possible to have FK that spans into child tables?
This is a well known (and documented, see [1]) deficiency. It's due to
the current implementation of indices, which are bound to exactly one
table, meaning they do return a position within the table, but cannot
point to different
I have the following query:
select array_accum(name) from (select name from placenames where
desig='crater' order by name desc) a;
with array_accum defined as:
CREATE AGGREGATE array_accum (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
Can I
order/aggregate thing is a general question.
Yes.
You can even do this with GROUP BY as long as the leading columns of the ORDER
BY inside the subquery exactly matches the GROUP BY columns.
In theory we can't promise anything about future versions of Postgres but
there are lots of people
The command you gave only shows currently
active users, not the historic peak of connections for instance. I'll
keep digging tha manual but would love any nudges in the right
direction, thanks!
Can you set up a snapshot in a cronjob? It would still only be sample
of a sample, but?
Pgs... like a warning that you can't do this;
begin
insert 1 --works
insert 2 --fails
commit
row 1 will exist in db (yes, no kidding). This will not work in pg,
which I now see is obviously correct.
This should either a FAQ for MS-SQL or Spring, but since PG does it
canonically it
Isn't the 'try' statement rather similar to a 'savepoint' command? I
realize it would be difficult to override the behaviour of try {...}
catch (...) {...}, but it shouldn't be too hard to wrap it somehow for
exceptions in database code.
Yes, but I believe the OP was getting two levels of his
it is all so easy with other dbs, but with postgresql
it is a nightmare... the only solution I can see is to remove the
declarative transactions in Spring and start using manual transactions
blocks around everything that could possibly go wrong... just because of
a quirk in postgresql
Hi all,
This is just a random question/idea (I tried googling, but couldn't
get an answer quickly):
Has anyone written a library to effect linear algebra types and
operations through Postgres? E.G., convert a float 2-d array to a
matrix, convert a float 1-d array to a vector, multiply them,
Hi all,
Take a look at pl/r, http://www.joeconway.com/plr/. This is a interface
to R: http://www.r-project.org/
I'm not familiar with this, but i think, this may be helpful for you.
Shoot -- I should have said that I knew about plr -- supposedly a
great project (maybe the reason there is no
... planning a simple coordinate system, where objects are described
as x, y and z. Are there any contribs or extensions available that can
help me with datatypes, calculation of length between two points, etc?
google postgis. It is for geographic stuff, so maybe overkill, but
maybe not.
http://www.postgresql.org/docs/8.2/static/datatype-geometric.html
Have you looked at these yet? If not, you asked your question
prematurely and should have read the docs. If so, in what respect do
they not work for you?
On 3/15/07, Robin Ericsson [EMAIL PROTECTED] wrote:
On 3/15/07, Webb
OK, I modified things to use interpolation. Here's the updated query:
explain UPDATE Transactions
SET previous_value = previous_value(id)
WHERE new_value IS NOT NULL
AND new_value ''
AND node_id IN (351, 169, 664, 240);
And
I would like to convert a mysql database with 5 million records and
growing, to a pgsql database.
All the stuff I have come across on the net has things like
mysqldump and psql -f, which sounds like I will be sitting forever
getting this to work.
Have you tried it? 5 million rows seem doable.
I am not able to look as closely as it deserves ...
... but I see two seq scans in your explain in a loop -- this is
probably not good. If you can find a way to rewrite the IN clause
(either de-normalizing through triggers to save whatever you need on
an insert and not have to deal with a set,
Well, I've tried to do massive UPDATEs as much as possible. But the
patterns that we're looking for are basically of the variety, If the user
clicks on X and then clicks on Y, but without Z between the two of them, and
if these are all part of the same simulation run, then we tag action X as
.
Heh. Sure thing. I wasn't sure how much detail to give when initially
posting.
Looks like enough to get the real experts on the list started :)
I will try to look again tommorrow, but I bet other folks have better
intuition than me.
How much concurrency is there on your database?
. I have _additional_
constraints to place on modifications done through
views, and trickyness involved in modifying the
underlying tables.
Write a function foo that returns a set, then a view: create view as
select * from foo(). Incorporate all the trickiness in the function,
including
on a view, you are probably not using SQL the way it
was intended (not that that is a bad thing, but ...)
Postgresql has rules which I *think* can rewrite select statements.
Rules are kind of a pain, but maybe what you want.
On 2/24/07, Webb Sprague [EMAIL PROTECTED] wrote:
. I have _additional_
Maybe his real goal all the backups readily available to be read by
my program (opening the backup read only) is to have a historical
record of what certain records looked like in the past.
What postgresql time travel? I have never used it, and it looks a
little bit unmaintained, but it
Here is the link to Elein's presentation:
http://www.varlena.com/GeneralBits/Tidbits/tt.pdf
What [about] postgresql time travel? I have never used it, and it looks a
little bit unmaintained, but it might be perfect with some tweaking:
---(end of
Is there a way to move a cursor in plpgsql in the same way as in
regular sql? ...
Wouldn't it be easier to list the parts in a random order (simply ORDER
BY RANDOM()) and then use modulo by number of actors (but there's no
ROWNUM so a loop is needed anyway). Something like...
I think you
Hi all,
Is there a way to move a cursor in plpgsql in the same way as in
regular sql? The function below would like to move the cursor back to
the start each time the cursor runs out of rows, creating pairs of
integers that are randomly put together.
The motivation for this is to randomly
I am trying to figure out how to use a regex and an ANY(), without any
luck, to determine if at least one element of an array (on the right)
matches the given constant pattern (on the left).
I think the problem is because the pattern expects to be on the right
side with the target on the left,
This is no small task.
But that a mans reach should exceed his grasp...
All of that being said, if you want to do it yourself, I would still
claim that you'd get there a lot faster adopting Andromeda, because all
you are really trying to do is embellish what we've already done.
The problem
So far, here are the candidates: Andromeda, Lazarus, and Rekall.
I was probably fairly inarticulate in my first post, but none of these
seem to meet my criteria for automatic generation of forms based on the
database definition. Most of the above frameworks have a good deal
more functionality
I don't want to revisit or be redundant... but is there a quick and
dirty and cross-platform system for developing user input forms for
Postgres? Ideally, I am interested in something such that you can give
it (it being something like a Python function) a table name,
resulting in a magically
Earlier I posted with my problems about the WAL logs eating up all my
diskspace. I tried the solutions offered--checkpoint after a big copy and
shortening the time between flushes. They helped somewhat.
Unfortunately, the problem snow seems to happen when I vacuum-analyze after a
big
We have a table with a lot of user sessions (basically end -time and
length of connection). We would like to query this table to count the max
number of simultaneous sessions, but we are stumped on how to do that.
The only thing I have been able to think of is to iterate over the entire
Does anybody know of a good way to COPY a file into a table if the data is
based on fixed width format? Do I just have to write some code with
scanf(Ick)? For example (Sorry about the poor ASCII art formatting):
| FIELD DESCRIPTIONLENGTH POSITION|
I had this problem with 7.0.3, but it cleared up completely with 7.1
W
James Thornton wrote:
Vacuum analyze keeps hanging here...
NOTICE: --Relation referer_log--
NOTICE: Pages 529: Changed 1, reaped 509, Empty 0, New 0; Tup 24306:
Vac 43000, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen
Hi all,
The problem:
I do a large bulk copy once a day (100,000 records of Radius data),
tearing down indices, truncating a large table that contains summary
information, and rebuilding everything after the copy. Over the course
of this operation, I can generate up to 1.5 gigs of WAL data in
Aahh! I have a million Perl scripts that rely on this syntax in the DBI
to connect to remote databases.
like:
$dbh=DBI-connect( "dbi:Pg:dbname=datab@remotehost",'wsprague','pass',
{RaiseError = 1, AutoCommit = 1})
or print STDERR "$DBI::errstrp";
Umm, do you have
I have a server for which PG_DATA = /home/maxtor, an NFS mounted
disk. Normally it works great, but when I try to vacuum, it takes
FOREVER. Should I not even try to use remote storage like this? Has
anybody else run into a similar problem?
Thanks in advance,
--
Webb Sprague
Programmer
O1
Hello all,
In my quest to learn PG and SQL programming, I have
created tables in a database "foo". I am able to
insert, select, etc just fine, but when I use "\dt" to
show them they don't appear. They ARE listed in the
system table "pg_tables", however.
I have also tried to createdb "test1"
69 matches
Mail list logo
