Re: [GENERAL] ECPG examples...
On Thu, Sep 01, 2005 at 02:10:04PM -0600, Cristian Prieto wrote: > Hello, I've been reading a little the ECPG (Embedded SQL in C) and the doc is > (I guess) very clear, but I cannot find any examples in the documentation, > any idea where to get examples? inside the pgsql source code? Yes, there us an example directory in the source code: .../src/interfaces/ecpg/test Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] same size VARCHAR or INT IX faster?
Matthew Peter wrote: same size VARCHAR or INT IX faster? i assume INT. The reason I ask is I was wondering what (if any) is the avg delay from one over the other? And benefit of one over the other? Thanks. If you want numbers, use INT. If you want text use a VARCHAR. It's probably difficult to come up with speed comparisons for "the same size" since varchar will have an overhead for the field-length as well as the number of characters. Even then, you'd have to account for client language and application overheads. In any case, optimising at this level is unlikely to be a good use of your time unless you really have reached the practical limits of available hardware. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] how to retrieve error message details
in PgAdmin, running wrong insert command causes error like ERROR: insert or update on table "dok" violates foreign key constraint "dok_klient_fkey" DETAIL: Key (klient)=(gg ) is not present in table "klient". Running same command through ODBC driver returns only first line. How to retrieve DETAIL line (key name and value) which violates referential integrity from Postgres after receiving this error ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Schema problems RedHat / SuSE 9.3 for version 7.4.8
Hi list/Michael,
Sorry I forgot "reply to all"
It proved to be a problem with the permissions on the table and view! So, the
error that was reported was completely different from the actual error. I do
not know how this can happen, but by making a direct connection to the
database within Zope, I was able to get the real error message.
I will investigate further how the reporting of the messages got confused. It
could be a problem in Zope or in psycopg. If I find something interesting I
will report back to the list.
Thanks for the help.
Dick
Op vrijdag 2 september 2005 00:04, schreef Michael Fuhr:
> [Please copy the mailing list on replies so others can contribute
> to and learn from the discussion. I've quoted more of your message
> than I ordinarily would because other people won't have seen it and
> they won't find it in the list archives.]
>
> On Thu, Sep 01, 2005 at 11:35:43PM +0200, Dick Kniep wrote:
> > After starting psql, and executing the query, without a begin, after the
> > query there is no search path
> >
> > SELECT set_config('search_path', '"' || t2.schema || '"', true) FROM
> > "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2 WHERE uid =
> > 'zon0023' AND t1.administratie_id = t2.administratie_id;
> > set_config
> > --
> > "adeuxproductie"
> > (1 row)
> >
> > cvix=# SHOW search_path;
> > search_path
> > --
> > $user,public
> > (1 row)
>
> Apparently you're in autocommit mode, which is the default for psql.
> Each statement is its own transaction, so you won't see the effects
> of set_config() when the third argument is true.
>
> > Executed with third parameter false:
> >
> > cvix=# SELECT set_config('search_path', '"' || t2.schema || '"', false)
> > FROM "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2 WHERE
> > uid = 'zon0023' AND t1.administratie_id = t2.administratie_id;
> > set_config
> > --
> > "adeuxproductie"
> > (1 row)
> >
> > cvix=# SHOW search_path;
> >search_path
> > --
> > "adeuxproductie"
> > (1 row)
> >
> > Also the same result when I have a "begin" before the first statement.
> > Which means that it seems to work correctly!
>
> Yep. If you're in a transaction block, or if you tell set_config()
> not to make the change local to the transaction, then you see the
> new setting take effect.
>
> > Also, a thing I hadn't checked before, is that the psql results on the 2
> > servers are the same. Which leads to my conclusion that the autocommit
> > settings are indeed different on the 2 servers.
>
> What do "SELECT version()" and "SHOW autocommit" show on both
> servers? If both servers are running 7.4 then they can't have
> different autocommit settings because 7.4 and later don't support
> server-side autocommit (it always shows "on" and you can't change
> it). Unless one of the servers is running 7.3, the autocommit
> settings must be on the client side. Are you using the same instance
> of the client to connect to both servers?
>
> > OK, next question, how do I get rid of the autocommit in my application?
> > I tried set autocommit to off; but that is deprecated.
>
> Using "SET autocommit" attempts to change the server-side setting,
> which was only supported in 7.3 (the developers removed it after
> deciding it had been a bad idea). How to disable autocommit on the
> client side depends on your client interface. What language and
> API are you using?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Question regarding FOUND
Greetings, I have a question regarding the use of the FOUND variable within a plpgsql function. I have a trigger fuction which executes a dynamic update on a different table with the EXECUTE statement. Here's the question: Will the FOUND variable be set, when using EXECUTE, as it would be with a normal UPDATE statement? Thanks for the input... ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] LOG: unexpected EOF within message length word
Hi, When I try to insert the data of size 40764 bytes(data type bytea) the call PQexecparams won't return and when I kill that one it gives The log:"LOG: unexpected EOF within message length word" Or "Log:Incomplete message transfer from client". If I try to insert data of size 40760 bytes (data type:bytea) Then it will insert the data succesfully. Thx in advance, Prasanna. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Question regarding FOUND
On Fri, Sep 02, 2005 at 08:51:41AM -0400, Terry Lee Tucker wrote: > > Will the FOUND variable be set, when using EXECUTE, as it would be with a > normal UPDATE statement? What happened when you tried it? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question regarding FOUND
I haven't tried it. It's a complicated trigger function with updates to other tables that are NOT dynamic in nature. If the EXECUTE statement doesn't set the FOUND variable, then I will be reading the result from a previous operation. I thought maybe somebody would know this already. On Friday 02 September 2005 09:14 am, Michael Fuhr saith: > On Fri, Sep 02, 2005 at 08:51:41AM -0400, Terry Lee Tucker wrote: > > Will the FOUND variable be set, when using EXECUTE, as it would be with a > > normal UPDATE statement? > > What happened when you tried it? > > -- > Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] LOG: unexpected EOF within message length word
On Fri, Sep 02, 2005 at 09:30:31AM +0530, Mavinakuli, Prasanna (STSD) wrote: > When I try to insert the data of size 40764 bytes(data type bytea) the > call PQexecparams won't return and when I kill that one it gives > The log:"LOG: unexpected EOF within message length word" > Or "Log:Incomplete message transfer from client". > > If I try to insert data of size 40760 bytes (data type:bytea) > Then it will insert the data succesfully. Works fine here -- could you post a simple but complete program that demonstrates the problem? Have you used a debugger or process trace to see what the program is doing? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Question regarding FOUND
Apparently, the FOUND variable is set to false regardless of the outcome of the dynamic update statement. I placed a call to GET DIAGNOSTICS var = ROW_COUNT. It returned 1. Question is, was that the result of a direct update previously coded in the same trigger, or is that the result of the dynamic update statement performed with EXECUTE? On Friday 02 September 2005 08:51 am, Terry Lee Tucker saith: > Greetings, > > I have a question regarding the use of the FOUND variable within a plpgsql > function. I have a trigger fuction which executes a dynamic update on a > different table with the EXECUTE statement. Here's the question: > > Will the FOUND variable be set, when using EXECUTE, as it would be with a > normal UPDATE statement? > > Thanks for the input... > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly -- Quote: 87 "To those who cite the First Amendment as reason for excluding God from more and more of our institutions every day, I say: The First Amendment of the Constitution was not written to protect the people of this country from religious values; it was written to protect religious values from government tyranny." --Ronald Reagan Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] lock problem
hi Richard ,folks Question: is normal this locks ? which is the reason of this? thanks for your time best regards MDC --- Richard Huxton escribió: > marcelo Cortez wrote: > > Richard > > > > Sorry for a delay > > > > question the select * from pg_stat_activity > > thows > > > > > datid|datname|procpid|usesysid|usename|current_query|query_start > > > 52800|"sume"|30124|1|"postgres"|""|"2005-09-01 > > 13:30:02.921844-03" > > > 52800|"sume"|30125|1|"postgres"|""|"2005-09-01 > > 13:37:21.631802-03" > > 52800|"sume"|30186|1|"postgres"|"SELECT > > c.actuacion_car AS c_actuacion, c.comentario1 || ' > ' > > || c.comentario2 || ' ' || c.comentario3 AS > > c_comentario FROM caratult AS c INNER JOIN > extractt AS > > t1 ON (c.id_extracto_car = t1.id_extracto) INNER > JOIN > > repartit AS r1 ON (c.id_reparticion_uc = > > r"|"2005-09-01 13:35:45.152586-03" > > > > and the select * from pg_locks > > relation|database|transaction|pid|mode|granted > > 53046|52800||30186|"AccessShareLock"|t > > ||159274343|30125|"ExclusiveLock"|t > > 73744|52800||30186|"AccessShareLock"|t > > 16759|52800||30125|"AccessShareLock"|t > > 53094|52800||30186|"AccessShareLock"|t > > 73770|52800||30186|"AccessShareLock"|t > > ||159274288|30186|"ExclusiveLock"|t > > 73824|52800||30186|"AccessShareLock"|t > > 53054|52800||30186|"AccessShareLock"|t > > 73726|52800||30186|"AccessShareLock"|t > > 53074|52800||30186|"AccessShareLock"|t > > 53049|52800||30186|"AccessShareLock"|t > > 53127|52800||30186|"AccessShareLock"|t > > 9567503|52800||30186|"AccessShareLock"|t > > 74274|52800||30186|"AccessShareLock"|t > > > > this queries show locks into 30816 pid or a'im > wrong? > > That's right - pid=30816 is the backend running the > SELECT > c.actuacion_car... query (see the pg_stat_activity > output). > > The only other locks mentioned are for pid=30125, > which I think are > where you're executing "SELECT * FROM pg_locks" - > bit puzzled as to why > the relation/database columns are blank though. > > In short - I can't see anything blocking your query. > What error message > was telling you that locks were causing a problem? > > -- >Richard Huxton >Archonet Ltd > ¡Llamá y ganá! Usá Yahoo! Messenger con Voz y participá del sorteo de un pasaje a cualquier lugar del mundo. Inscribite aquí: http://messenger.yahoo.com/ar/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question regarding FOUND
Terry Lee Tucker <[EMAIL PROTECTED]> writes: > Will the FOUND variable be set, when using EXECUTE, as it would be with a > normal UPDATE statement? The documentation is pretty specific about which plpgsql statements set FOUND, and I don't see EXECUTE in that list ... http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] LOG: unexpected EOF within message length word
"Mavinakuli, Prasanna (STSD)" <[EMAIL PROTECTED]> writes: > When I try to insert the data of size 40764 bytes(data type bytea) the > call PQexecparams won't return and when I kill that one it gives > The log:"LOG: unexpected EOF within message length word" > Or "Log:Incomplete message transfer from client". > If I try to insert data of size 40760 bytes (data type:bytea) > Then it will insert the data succesfully. I think the odds are about 100:1 that this is a bug in your own code. However, if you want to send in a self-contained test case, we'll be glad to take a look at it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] lock problem
marcelo Cortez wrote: hi Richard ,folks Question: is normal this locks ? which is the reason of this? As I said - I don't see any locks that could cause you problems. Can you provide the error message showing locks timing out? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Question regarding FOUND
I looked at the documentation before I ever posted anything. I didn't know how EXECUTE works and I actually thought the UPDATE statement itself would set the variable. Thanks for the reply. On Friday 02 September 2005 10:47 am, Tom Lane saith: > Terry Lee Tucker <[EMAIL PROTECTED]> writes: > > Will the FOUND variable be set, when using EXECUTE, as it would be with a > > normal UPDATE statement? > > The documentation is pretty specific about which plpgsql statements set > FOUND, and I don't see EXECUTE in that list ... > http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGS >QL-STATEMENTS-DIAGNOSTICS > > regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] REVOKE question
Hello, I have encountered on (for me) wierd thing. When dropping an user, the database will not forget his permissions. After his recreation he has the original permissions. I use an approach of dropping all users when recreating the database environment and user recreation to avoid any unwanted/temporary changes to permissions. Is there any way, how to revoke all permission for the user on any type in any schema in the database? I think this is essential for securying of access control of users. I tried to look in the doc, but found nothing about that. Thank you, bohdan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] REVOKE question
On Fri, Sep 02, 2005 at 05:31:54PM +0200, Bohdan Linda wrote: > Hello, > > I have encountered on (for me) wierd thing. When dropping an user, the > database will not forget his permissions. After his recreation he has the > original permissions. Known problem, partially fixed in 8.1. (At least it won't allow you to drop the user if it owns something, or is mentioned in an ACL.) -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "Some men are heterosexual, and some are bisexual, and some men don't think about sex at all... they become lawyers" (Woody Allen) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Self creating tables
I have a question to ask regarding the setting up of tables. I am developing Postgresql for an engineering application. This application will have an initial user succeeded by multiple users in multiple engineering disciplines downstream. Level One What I would like to do is to have a minimum number of tables gathering information from the base engineering document (P&ID). Each physical element produced on the P&ID drawing will find its way to one row of one of three tables. The user will be responsible for establishing the category (pipe, vessel, etc.) that each element falls into. (Each table represents a particular function of the P&ID.) Level Two I could create a separate table for each category of elements (pipe, vessel etc.) with columns that represent the second level information the user requires. The challenge is that the P&ID is a creative document. There is always a distinct possibility of the user inserting an element that we haven’t considered. The above approach would force him to stop what he is doing and create a new table(s) for the new element(s). Is there a method, in SQL, of setting up a table for the second level that can be used as a template for each category of elements found in the rows of tables in the first level? (For instance – set up a table for”pipes” and have the category “vessels” initiate its own table.) I’m new to SQL so, if this is possible, I may only need the terminology used in order to find this methodology in my Postgresql book or through the Postgre on-line help. Thanks Bob Pawley
Re: [GENERAL] Self creating tables
On Fri, 2005-09-02 at 09:51 -0700, Bob Pawley wrote: > regarding the setting up of tables > ... > I could create a separate table for each category of elements ... The > challenge is ... a distinct possibility of the user inserting an > element that we haven’t considered. The above approach would force > him to stop what he is doing and create a new table(s) for the new > element(s). Your application should be able to create tables for the user. The user should not need to exit your app, get to an SQL command line, and issue CREATE TABLE statements. The CREATE TABLE statement is just another SQL statement, like SELECT, INSERT, etc. Try using your API (whatever that is -- this is another decision you may still have to make) to issue CREATE TABLE and see how it works. > in my Postgresql book or through the Postgre on-line help. Typically the database is referred to either as "Postgres" or as "PostgreSQL," but not as "Postgre." ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Trouble with bytea in SPI...
First at all, thanks a lot for your help with my trouble, it was very
helpfull...
This is my complete code:
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include
PG_FUNCTION_INFO_V1(myspi);
Datum
myspi(PG_FUNCTION_ARGS)
{
int ret;
bool isnull;
bytea *val;
void *plan;
Oid *karg;
Datum newval[1];
// -- La parte de numeros aleatorios va aki
gsl_rng_type *T;
gsl_rng *r;
void *stat;
int res;
int num;
num = PG_GETARG_INT(0);
if(PG_ARGISNULL(0)) {
PG_RETURN_NULL();
}
gsl_rng_env_setup();
T = gsl_rng_default;
r = gsl_rng_alloc(T);
ret = SPI_connect();
karg = (Oid *) palloc(sizeof(Oid));
ret = SPI_exec("SELECT st FROM rng_seeds", 1);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
TupleDesc tupdesc = SPI_tuptable->tupdesc;
SPITupleTable *tuptable = SPI_tuptable;
val = DatumGetByteaP(SPI_getbinval(tuptable->vals[0], tupdesc, 1,
&isnull));
karg[0] = SPI_gettypeid(tupdesc, 1);
}
stat = r->state;
memcpy(stat, VARDATA(val), gsl_rng_size(r));
res = (int) gsl_rng_uniform_int(r, num);
/* Aki retorno el valor modificado a su respectiva celda */
memcpy(VARDATA(val), stat, gsl_rng_size(r));
plan = SPI_prepare("UPDATE rng_seeds SET st=$1", 1, karg);
if (!plan)
elog(ERROR, "I don't know what happened!");
plan = SPI_saveplan(plan);
newval[0] = PointerGetDatum(val);
ret = SPI_execp(plan, newval, NULL, 0);
SPI_finish();
gsl_rng_free(r);
PG_RETURN_INT32(res);
}
And thanks to all of you it works as expected, the theory behind this is the
following:
gsl random library has a lot different kind of random number generators and
support for some random distributions, so I decide to implement it for a
project I've been working on. I can store the "state" of a random number to
use it to generate the next one. The state is a segment of the memory and it
is stored in a bytea field, so I decided to create a table and in the future
add a name field and handle it as a sequence (ala nextrandval('name'))...
Right now it works just with one field and I guess it is working well, but I
am very worried about the performance of SPI_execute() and SPI_execp(). I
read in the Developer FAQ something about accessing the data directly from
the backend code. If it is like this I would like to get more infor about
how to use SearchSysCache() and heap_beginscan().
Do you think I need to implement such thing to improve performance? any idea
in how to improve my approach to this trouble?
Thanks a lot for your answer!
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Michael Fuhr" <[EMAIL PROTECTED]>
Cc: "Cristian Prieto" <[EMAIL PROTECTED]>;
Sent: Thursday, September 01, 2005 9:51 PM
Subject: Re: [GENERAL] Trouble with bytea in SPI...
Michael Fuhr <[EMAIL PROTECTED]> writes:
On Thu, Sep 01, 2005 at 08:23:31PM -0600, Cristian Prieto wrote:
Hello, I've been working just a little with SPI in a few stored
functions, this is a model of my SP:
Please post a real example instead of a "model."
Also, it's good to make at least some minimal effort with gdb to find
out where your code is crashing. A backtrace from the core dump
(or from catching the signal interactively) often tells a lot.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] operators, operator classes, Btree and index usage
Hello All, I have a following question: I'm using some set of queries like: SELECT * FROM (SELECT my_function(ra, dec, 0.001) AS ipix1, ra1, dec1 FROM table1) AS jtable1, table2 WHERE table2.ipix>=ipix1[1] AND table2.ipix<=ipix1[2] ); ipix is bigint column, on which the Btree index is created or dinamically created selects containing a lot of OR'ed conditions like: select * from my_table WHERE (ipix < 44 AND ipix > 40) OR (ipix <88 AND ipix>66) OR ipix is bigint column,, on which the Btree index is created. I'm interested in simplifying those queries and introducing the operator doing something like this: my_operator(bigint x, bigint[] arr) checking the condition: ((x>arr[1]) AND (xarr[3]) AND (xhttp://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] operators, operator classes, Btree and index usage
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > I'm interested in simplifying those queries and introducing the operator > doing something like this: > my_operator(bigint x, bigint[] arr) > checking the condition: > ((x>arr[1]) AND (xarr[3]) AND (x
Re: [GENERAL] Check if SELECT is granted
See the has_table_privilege() function.
Thanks, but how does it work ?
select has_table_privelege('public.mytable', 'select');
ERROR: function has_table_privelege("unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
Poul
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Trouble with bytea in SPI...
On Fri, Sep 02, 2005 at 12:17:09PM -0600, Cristian Prieto wrote: Hey Cristian, > Right now it works just with one field and I guess it is working well, but > I am very worried about the performance of SPI_execute() and SPI_execp(). I > read in the Developer FAQ something about accessing the data directly from > the backend code. If it is like this I would like to get more infor about > how to use SearchSysCache() and heap_beginscan(). > > Do you think I need to implement such thing to improve performance? any > idea in how to improve my approach to this trouble? While SPI does impose some overhead, code-wise it is certainly appropiate for what you are doing. SearchSysCache() and the like is reserved for system catalogs, and I don't think you want to recompile the whole of Postgres just to get some improvement there. No need to mention the fact that your Postgres would be incompatible with everyone else's, and un-backup-able. Certainly not a road I'd go. Using heap_beginscan et al would be almost the same as using SPI. I doubt there's a lot of performance to be gained that way ... or maybe there is, but you'd pay in maintenability and obscure bugs, and you'll lose the future improvements to the optimizer, etc. Just be sure to use VACUUM and ANALYZE appropiately, keep well defined indexes, and you shouldn't need much else. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "The important things in the world are problems with society that we don't understand at all. The machines will become more complicated but they won't be more complicated than the societies that run them."(Freeman Dyson) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Check if SELECT is granted
On Fri, Sep 02, 2005 at 10:39:14PM +0200, Poul Møller Hansen wrote:
> >
> >See the has_table_privilege() function.
>
> Thanks, but how does it work ?
>
> select has_table_privelege('public.mytable', 'select');
> ERROR: function has_table_privelege("unknown", "unknown") does not exist
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.
It helps if you spell "privilege" correctly ;-)
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Schema overlay question
Hi All, A simple question to the list. We are designing a database, the database has three major components (each component with 4-8 tables) two components are ready, is it possible to 'overlay' the third one later? or Do i have to drop all the components (along with data!!!) and then add the third component and then build the entire DB? What i am saying is some tables are existing, could i later add a bunch of tables at a later date without dropping the entire database? Thanks for your help. Regards, Hrisih
Re: [GENERAL] Schema overlay question
Hrishikesh Deshmukh <[EMAIL PROTECTED]> writes: > What i am saying is some tables are existing, could i later add a bunch of > tables at a later date without dropping the entire database? Sure, you can do CREATE TABLE at any time. -Doug ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Schema overlay question
On Fri, Sep 02, 2005 at 05:28:11PM -0400, Hrishikesh Deshmukh wrote: > We are designing a database, the database has three major components (each > component with 4-8 tables) > two components are ready, is it possible to 'overlay' the third one later? > or > Do i have to drop all the components (along with data!!!) and then add the > third component and then build the entire DB? > What i am saying is some tables are existing, could i later add a bunch of > tables at a later date without dropping the entire database? What exactly do you mean by "overlay"? As far as the database is concerned, you can create tables any time you want: today, tomorrow, or a year from now. Is there some specific case you're concerned about? If so then please elaborate. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] operators, operator classes, Btree and index usage
On Fri, 2 Sep 2005, Tom Lane wrote: > "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > > I'm interested in simplifying those queries and introducing the operator > > doing something like this: > > > my_operator(bigint x, bigint[] arr) > > checking the condition: > > ((x>arr[1]) AND (xarr[3]) AND (x > This could be made to work if you define the above as an inline-able SQL > function. Hacking operator classes won't do it though. Thank you, Tom! Great! I didn't know that Postgres can inline the SQL functions (In fact this is because the only place in the documentation mentioning about inlining SQL functions is the changelog of the postgres 7.4 :). With Best Regards, Sergey * Sergey E. Koposov Max-Planck Institut fuer Astronomie Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Schema overlay question
HI All, What i meant was when the third component of tables are ready and i can create tables any time what happens to relationships when i am building the third component tables? How smooth the process will be? Regards, Hrishi On 9/2/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Fri, Sep 02, 2005 at 05:28:11PM -0400, Hrishikesh Deshmukh wrote:> We are designing a database, the database has three major components (each> component with 4-8 tables)> two components are ready, is it possible to 'overlay' the third one later? > or> Do i have to drop all the components (along with data!!!) and then add the> third component and then build the entire DB?> What i am saying is some tables are existing, could i later add a bunch of > tables at a later date without dropping the entire database?What exactly do you mean by "overlay"? As far as the database isconcerned, you can create tables any time you want: today, tomorrow, or a year from now. Is there some specific case you're concernedabout? If so then please elaborate.--Michael Fuhr
Re: [GENERAL] Schema overlay question
On Fri, Sep 02, 2005 at 07:16:04PM -0400, Hrishikesh Deshmukh wrote: > What i meant was when the third component of tables are ready and i can > create tables any time what happens to relationships when i am building the > third component tables? How smooth the process will be? What relationships? How do you anticipate the "third component" tables possibly interfering with tables that have already been created? It's still not clear what you're concerned about, since one can certainly create and use one set of tables, then at some later time create and use another set of tables. Apparently there's something about creating that later set of tables that concerns you, but we don't know what that concern is based on. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] same size VARCHAR or INT IX faster?
I assumed as much. Now's the time for me to optimize so I'd rather know and make optimizations accordingly, than step blindly. Thanks for the reply. As always, your a big help. --- Richard Huxton wrote: > Matthew Peter wrote: > > same size VARCHAR or INT IX faster? i assume INT. > The > > reason I ask is I was wondering what (if any) is > the > > avg delay from one over the other? And benefit of > one > > over the other? Thanks. > > If you want numbers, use INT. If you want text use a > VARCHAR. > > > It's probably difficult to come up with speed > comparisons for "the same > size" since varchar will have an overhead for the > field-length as well > as the number of characters. > > Even then, you'd have to account for client language > and application > overheads. > > In any case, optimising at this level is unlikely to > be a good use of > your time unless you really have reached the > practical limits of > available hardware. > > -- >Richard Huxton >Archonet Ltd > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map > settings > Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] LOG: unexpected EOF within message length word
On Sat, Sep 03, 2005 at 09:29:20AM +0530, Mavinakuli, Prasanna (STSD) wrote: > here is the sample code which works fine and prints column > lengths in HP-UX PA machines and breaks in HP-UX IA machines. The code you posted works for me with PostgreSQL 8.0.3 on FreeBSD 4.11-STABLE/i386 and Solaris 9/sparc. I don't know if there are any issues with HP-UX IA; is there anything else different about the two environments? What versions of PostgreSQL are you using? Aside from not being a complete program, the code you posted doesn't quite match the problem description you gave initially: you said you could send 40760 bytes, but that sending 40764 bytes caused the connection to hang. Is that an accurate description? The code you posted sends 300 bytes and several additional columns that might not be relevant to the problem. > I tried tusc (I sent that o/p in previuos mail. I haven't seen that -- did you send it to the mailing list? > Client opens a socket and starts to write.but after some send receive it > goes to SLEEPING state) How much data is written before this happens? Are you using a local (Unix socket) connection or a TCP connection? If the latter, have you run a sniffer on the connection to see if it shows anything unusual (e.g., a closed window)? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
