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 s
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" = b.da
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),
> 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 (pgsql-general@postgr
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 programme
> 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
insid
> 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
>
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 li
>> 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
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 t
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":
http://www.po
> 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:
http://www.post
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 a
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.
> > 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 do
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
> I have the following function:
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. The i
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 '
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 coul
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
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=*"(
> > > 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
> 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
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 everyth
> ...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
TED]> 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/ MAT
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
EVISION
>
>
> It works as follows
> When you make commit on SVN it runs the hook script which makes db
> dump (look at pg_dump options).
>
> Regards,
> Blazej
>
> 2008/1/16, Webb Sprague <[EMAIL PROTECTED]>:
>
> > In another thread, someone mentioned writ
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 wher
>
> 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 -bash
> >
> > ... "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 Postgr
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
autho
> ... 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 di
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 p
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 coun
> 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?
>
> --
> 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
> 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 i
> It is still a possible point of confusion, but I am starting to think
> that pgsql has it right, and mssql has it wrong. (I feel betrayed!) This
> issue probably deserves a prominant place in the FAQ!
Betrayed? yes. Surprised? I hope not :)
I think your driver (JDBC? or?) is doing autocommit
> 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,
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 ma
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, tak
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
... 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. Ther
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 he
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, o
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 doab
.
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?
--
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
be
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:
&g
. 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 var
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 broadcast)--
>> 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, b
> 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 a
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 ass
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, bu
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 w
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 th
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 app
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 delete.
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
table
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, MinL
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
pg
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::errstr";
Umm, do you hav
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" aft
A couple of days ago I tried to create a database and
got an error to the effect that I couldn't do that (no
more specific). I then tried to mkdir in my DATA
directory for a new database (as root), and I got
"Can't make directory--not enough space in DIR"). I
deleted everything and then did initd
71 matches
Mail list logo
