[SQL] REINDEX DATABASE
Hello Would you like to advice to use REINDEX DATABASE on regular basis ? if (yes) how it should be connected with VACUUM FULL ANALYZE which is run regularly ? (reindex before vacuum or vacuum before reindex?) else haw to determine _when_ to run REINDEX ? many thanks ---(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: [SQL] a "catch all" type ... such a thing?
Marc G. Fournier wrote: Are there any data types that can hold pretty much any type of character? UTF-16 isn't supported (or its missing from teh docs), and UTF-8 doesn't appear to have a big enough range ... PLEASE Note: type of caracter is generally not a matter of _datatype_ ---(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: [SQL] How do I convert an integet to a timestamp?
Wei wrote: Hi. I followed the doc and tried "select CAST(1126547334 AS timestamp)" and I only got an error response that says: ERROR: cannot cast type integer to timestamp without time zone. What is the proper way to do the conversion? select 123456789::abstime::timestamp; ---(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: [SQL] catching errors in function
i have written function in which while executing it may throw error. if the error thrown i want rollback the transaction if not i want commit. BEGIN; SELECT you_function(args); ... many sql commands; ... COMMIT; that's all if your function raises an error whole transaction will be aborted when the COMMIT is called. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] query to file
Judith Altamirano Figueroa wrote: Excuse me, how can I send a query to a file?, for example the result of this query: select id_articulo, articulo, color_code from articulos; if you mean psql \o filename and try to read output of a command \? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] what is going on in the PostgreSQL
Hello all ! look template1=> \set a select template1=> \set b 1 template1=> :a :b; column -- 1 hope this is only psql's feathure not the server's one; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Permission to Select
Hi all
the serious problem with permissions is encountered
NOTE: the following example is really useful but there is no room to
describe it's use.
db=# CREATE USER u;
db=# CREATE TABLE t (i int, a text);
db=# REVOKE all ON t FROM u;
db=# GRANT update,insert,delete ON t TO u;
db=# \c - u
db=> INSERT INTO t VALUES (1,'x');
INSERT
db=> UPDATE t SET a='y' WHERE i=1;
ERROR: Permission denied for relation t;
db=> UPDATE t SET a='y';
UPDATE
1) The user "u" is permitted but unable to perfom the operation !
2) A user is able to update WHOLE table but unable to update ANY part of
it !
Please examine the following patch and make your judgment:
--- src/backend/executor/execMain.c.orig 2005-11-22 1:23:08.0 +0300
+++ src/backend/executor/execMain.c 2006-02-17 13:19:29.0 +0300
@@ -460,6 +460,16 @@
booldo_select_into;
TupleDesc tupType;
+ if ( operation == CMD_UPDATE || operation == CMD_DELETE )
+ {
+ ListCell *l;
+ foreach(l, parseTree->rtable)
+ {
+ RangeTblEntry *rte = lfirst(l);
+ rte->requiredPerms ^= ACL_SELECT;
+ }
+ }
+
/*
* Do permissions checks. It's sufficient to examine the query's top
* rangetable here --- subplan RTEs will be checked during
---(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: [SQL] Permission to Select
Tom Lane wrote: "Eugene E." <[EMAIL PROTECTED]> writes: db=# REVOKE all ON t FROM u; db=# GRANT update,insert,delete ON t TO u; db=# \c - u db=> INSERT INTO t VALUES (1,'x'); INSERT db=> UPDATE t SET a='y' WHERE i=1; ERROR: Permission denied for relation t; db=> UPDATE t SET a='y'; UPDATE This behavior is correct and as documented in the UPDATE reference page: Good if you have a strange behavior - just document it. quite good. You must have the UPDATE privilege on the table to update it, as well as the SELECT privilege to any table whose values are read in the expressions or condition. This means that some privileges are NOT INDEPENDENT. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Permission to Select
PFC wrote: What information can be retrieved from a structure by being able to update all rows? Write a plpgsql function snoop(x) which inserts x into a table 'log' created by you, and also returns x. UPDATE users SET password=snoop(password). Read log table. Done. This trick is available _NOW_. (in the current state of permission system) ---(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: [SQL] Permission to Select -- I am wrong
Eugene E. wrote: PFC wrote: What information can be retrieved from a structure by being able to update all rows? Write a plpgsql function snoop(x) which inserts x into a table 'log' created by you, and also returns x. UPDATE users SET password=snoop(password). Read log table. Done. This trick is available _NOW_. (in the current state of permission system) I AM WRONG ! sorry. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] PostgreSQL Handling of Special Characters
Christian Paul B. Cosinas wrote: Hi Markus, Can you please elaborate more on this. I'm really lost. Be sure that postgresql ITSELF is handling all chars transparently except ZEROES. Look for the error in the media layer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] have you feel anything when you read this ?
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-19.html --- cut --- mysql no longer terminates data value display when it encounters a NUL byte. Instead, it displays NUL bytes as spaces. (Bug #16859) --- cut --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
PFC wrote: have you feel anything when you read this ? Business as usual... It's more fun to grep "crash" on this page, which gets about 27 results... i am not trying to fight against or for any brandname: not Mesql nor postgres. just sed 's/MySQL/SomeDBMS/g' and concentrate on the fact: that even so stupid DBMS handling NULs properly. :-) ---(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: [SQL] have you feel anything when you read this ?
Peter Eisentraut wrote: Eugene E. wrote: PFC wrote: have you feel anything when you read this ? Business as usual... It's more fun to grep "crash" on this page, which gets about 27 results... i am not trying to fight against or for any brandname: not Mesql nor postgres. just sed 's/MySQL/SomeDBMS/g' and concentrate on the fact: that even so stupid DBMS handling NULs properly. :-) So printing a space is "properly"? Curious ... you may decide to print something else, aint'you ? BUT if they print them then they at least OUTPUT them. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
Peter Eisentraut wrote: Eugene E. wrote: you may decide to print something else, aint'you ? BUT if they print them then they at least OUTPUT them. I'm not sure what you are getting at here. The only data type in PostgreSQL that has a notion of null bytes is bytea, and bytea prints out null bytes in unambigious form. the bytea does not output NULs at all. don't mock me. Note that printing out a space will lose the null byte on restore, ok, if you or they or me miscall OUTPUT "the printing" then "print" NUL-byte itself to preserve it on restore. ---(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: [SQL] have you feel anything when you read this ?
Peter Eisentraut wrote:
Eugene E. wrote:
the bytea does not output NULs at all.
don't mock me.
peter=# create table test (a bytea);
CREATE TABLE
peter=# insert into test values ('a\\000b');
INSERT 0 1
peter=# select * from test;
a
a\000b
are you kidding ?
where is NUL-byte in "a\000b" ???
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
Alvaro Herrera wrote: Achilleus Mantzios wrote: dynacom=# SELECT '\000\150\145\154\154\157'::text; text -- (1 row) dynacom=# Oops! text is not bytea. source says: typedef text varlena; typedef bytea varlena; :-) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] have you feel anything when you read this ?
Achilleus Mantzios wrote:
a
a\000b
are you kidding ?
where is NUL-byte in "a\000b" ???
Null byte is a byte of value zero,
and allow me to say that the \000 in "a\000b" is exactly this.
if ("\0"=="\\000")
printf("congratulations!!!");
NOTE:
I am not care about a _display_ NUL-byte on a screen !
weither it'll be "space" or "some escape sequence"
the problem is: you'll get this four byte sequence '\000' _instead_ of
NUL-byte anyway.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
Alvaro Herrera wrote: Eugene E. wrote: Alvaro Herrera wrote: text is not bytea. source says: typedef text varlena; typedef bytea varlena; This means that as far as the C type system is concerned, both bytea and text are treated as "struct varlena". It doesn't mean that they are processed by the same input/output functions, which they aren't. NUL bytes are preserved in bytea, and used as terminators in text. I don't find this surprising at all, do you? I do found surprising it. since both (text and bytea) I/O functions has CSTRING arguments and resut type. - this only means a user should perform some unescaping on the bytea value he got. THE SAME THING he should do with a string value if he decide to use type text and to escape NUL-bytes before input. then what a difference bitween those types except strlen() ? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] have you feel anything when you read this ?
I wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. You wrote: Your client library should take care of escaping and de-escaping. We both agree as you see. Then i am asking: WHY should a client take care of de-escaping ? Why not to get his data unchanged ? If i forced to de-escape when using bytea then i may use text with the same escaping/de-escaping AS WELL. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
PFC wrote: I wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. You wrote: Your client library should take care of escaping and de-escaping. We both agree as you see. Then i am asking: WHY should a client take care of de-escaping ? Why not to get his data unchanged ? I can understand why you say that for something as simple as a BYTEA, but if the value to be passed to the client is an ARRAY of geometric types or something Who said "array" ? I just want to restore _one byte_ from bytea storage. that's all. Exporting data from postgres in binary is only useful to C programmers Serious judgment ! Extremely seriuos... nonetheless C programmers could not do this. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
Scott Marlowe wrote: On Mon, 2006-03-20 at 02:06, Eugene E. wrote: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-19.html --- cut --- mysql no longer terminates data value display when it encounters a NUL byte. Instead, it displays NUL bytes as spaces. (Bug #16859) --- cut --- Everyone here realizes that this is a mysql CLIENT bug, not server side. i.e. if you're writing an application and request that binary text string, you'll get it with nuls in it, just like you put in. Now, I'm not sure that turning nulls into spaces is the best way to handle this in the client. In fact, I'm sure it's not. But this is not a server bug, it's a client bug. I was not sure about MySQL, thank you for your explaination. This ensures me that even MySQL server handles NUL-bytes properly regardless to client problems. ---(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: [SQL] Permission to Select
Markus Schaber wrote: Hi, Eugene, Eugene E. wrote: This means that some privileges are NOT INDEPENDENT. No, it means that the UPDATE operation needs both UPDATE and SELECT privileges. Markus thanx. I already clear this to me. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
Peter Eisentraut wrote:
Eugene E. wrote:
the problem is: you'll get this four byte sequence '\000' _instead_
of NUL-byte anyway.
What you seem to be missing is that PostgreSQL data can be represented
in textual and in binary form. What you in psql is the textual form.
If you want the binary form you need to select it. Then you can pass
the exact bytes back and forth.
your sentence is not true.
I can not select exact bytes even if i use BYTEA type
the folloiwing tiny C-program shows this pretty clear
#include
#include
#include "libpq-fe.h"
int
main (void)
{
PGconn * conn;
PGresult * res;
char * val;
char * l;
intlen;
conn = PQconnectdb("user=scott password=tiger dbname=test_db");
PQexec(conn, "CREATE TABLE t (a BYTEA)");
PQexec(conn, "INSERT INTO t VALUES ('ab000cd')");
res = PQexec(conn, "SELECT a,length(a) FROM t");
val = PQgetvalue(res,0,0);
l = PQgetvalue(res,0,1);
len = PQgetlength(res,0,0);
printf("what_we_retrive='%s' its_value_length=%i but
orig_length=%s\n",val,len,l);
PQclear(res);
PQfinish(conn);
return 0;
}
---(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: [SQL] have you feel anything when you read this ?
Stephan Szabo wrote: On Fri, 31 Mar 2006, Eugene E. wrote: Peter Eisentraut wrote: Eugene E. wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. What you seem to be missing is that PostgreSQL data can be represented in textual and in binary form. What you in psql is the textual form. If you want the binary form you need to select it. Then you can pass the exact bytes back and forth. your sentence is not true. I can not select exact bytes even if i use BYTEA type No, that is still using the textual form. If you use PQexecParams and set the last argument to show you want binary data, you should get binary data. Documentation says: === PQexecParams Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text. === How should i use this func to change so-called "textual form" of a select-result to so-called "binary form" ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
Stephan Szabo wrote:
On Tue, 4 Apr 2006, Eugene E. wrote:
Stephan Szabo wrote:
On Fri, 31 Mar 2006, Eugene E. wrote:
Peter Eisentraut wrote:
Eugene E. wrote:
the problem is: you'll get this four byte sequence '\000' _instead_
of NUL-byte anyway.
What you seem to be missing is that PostgreSQL data can be represented
in textual and in binary form. What you in psql is the textual form.
If you want the binary form you need to select it. Then you can pass
the exact bytes back and forth.
your sentence is not true.
I can not select exact bytes even if i use BYTEA type
No, that is still using the textual form. If you use PQexecParams and set
the last argument to show you want binary data, you should get binary
data.
ok
then i am using PQexecParams
the following tiny program shows a wonderful lameness...
#include
#include
#include "libpq-fe.h"
int
main (void)
{
PGconn * conn;
PGresult * res;
char * val;
char * l;
intlen;
conn = PQconnectdb("user=scott password=tiger name=test_db");
PQexec(conn, "CREATE TABLE t (a BYTEA)");
PQexec(conn, "INSERT INTO t VALUES ('ab000cd')");
res = PQexecParams(conn, "SELECT a,length(a) FROM t", 0, NULL, NULL,
NULL, NULL, 1);
val = PQgetvalue(res,0,0);
l = PQgetvalue(res,0,1);
len = PQgetlength(res,0,0);
printf("what_we_retrive='screened' its_value_length=%i but
orig_length=%s\n",len,l);
PQclear(res);
PQfinish(conn);
return 0;
}
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
Stephan Szabo wrote: On Wed, 5 Apr 2006, Eugene E. wrote: Stephan Szabo wrote: On Tue, 4 Apr 2006, Eugene E. wrote: Stephan Szabo wrote: On Fri, 31 Mar 2006, Eugene E. wrote: Peter Eisentraut wrote: Eugene E. wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. What you seem to be missing is that PostgreSQL data can be represented in textual and in binary form. What you in psql is the textual form. If you want the binary form you need to select it. Then you can pass the exact bytes back and forth. your sentence is not true. I can not select exact bytes even if i use BYTEA type No, that is still using the textual form. If you use PQexecParams and set the last argument to show you want binary data, you should get binary data. ok then i am using PQexecParams the following tiny program shows a wonderful lameness... What lameness? The fact that you're trying to use a binary (network order maybe) integer as a string? That's not the fault of PQexecParams but of the code calling it. You're right ! That's is not a fault of PQexecParams at all. That's the fault of its design. (I pretty know why an integer has been not displayed, but why they designed this function that way ? i do not know) It'd be nice for ease of use to be able to say, give me this column (the integer) as a string and this column (the bytea) as binary, I always want to retrieve TEXT (and mostly INT) in TEXTUAL-FORM, and BYTEA always in BINARY-FORM. (at least by defaul) WHY SHOULD I RETRIVE A SINGLE BYTEA FIELD IN A SEPARATE QUERY ??? Look: if i define a field as of type BYTEA, doesn't it mean the field tends to store binary data ? If does, then WHY they (by default) convert its value to TEXTUAL-FORM which is not needed by default -- i already inform the server: "i want some BINARY to in and out" when i have choosed the type BYTEA ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
Praescriptum: If my english is ugly and something is written unclear, please complaint, and i'll try to rephrase. anyway i am trying to be understood. I said >> WHY SHOULD I RETRIVE A SINGLE BYTEA FIELD IN A SEPARATE QUERY ??? Stephan Szabo wrote: What would you expect it to do given a single result format argument? If you want to propose a new function (set of functions) that have different behavior, make a coherent proposal. > Statements like it should > do X because I want it to aren't coherent proposals. AFAIK, they convert each value before put it to a result set. I propose to do the following convertion to the textual-form for bytea values: X->X where X is byte [0..255] Expect to get asked why bytea is special _Because each type is special._ And at the same time they made bytea MORE special than any other type. Look: every type has many representations for its values, some are obvious some are more usefull, some are less useful. they define very useful and obvious representations for all the types but BYTEA. (They call those representations "textual-form".) and the input of a value demands escaping (we all undersdand why) and for each type the following equality is TRUE: some_data == OUTPUT(INPUT(ESCAPE(some_data))) but for the BYTEA this equality is FALSE ! Why BYTEA is so special ? every value of every type is expected to be given to a client UNCHANGED. I expect a value of BYTEA to be unchaged too. why should integer be passed as a string given because it is not causing problems, as well as if it be passed in any other common form. And i ask you: why integer is actually passed as a string (decimal notation) ? why not to define your own unique more_sofisticated representation ? (as for bytea is defined.) And finally Why so special textual-form defined for bytea ? Why not to leave every byte unchanged, since user knows what kind of data he got. P.S. changing a format of a whole result-set is not a solution for a field-type-dependent problem. ---(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: [SQL] have you feel anything when you read this ?
Stephan Szabo wrote: What would you expect it to do given a single result format argument? If you want to propose a new function (set of functions) that have different behavior, make a coherent proposal. > Statements like it should > do X because I want it to aren't coherent proposals. AFAIK, they convert each value before put it to a result set. I propose to do the following convertion to the textual-form for bytea values: X->X where X is byte [0..255] Okay, now pass that to strcmp or a %s format. AFAIK, the "textual-form" of values is meant to be a c-string. "ab\0cd\0" is not a c-string containing ab\0cd, it's a c-string containing ab. WHY strcmp ?! do you really think the user is a fool ? if the user declared something "binary", he obviously knows what he has done. WHY c-string ? the user only wants to get PGresult structure. Since this structure provides a length of each value, you have no need in c-string. Why do think the user needs it ? "textual-form" is just a name of actually existent convertion rule. i am not trying to find out a philosophy here. I think I don't exactly agree with this description, but I'm unclear exactly what you're saying. Are you saying that textual-form is the useful representation, or are you saying that textual-form is the representation and it is useful? the actual representasion of most types is pretty useful. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] have you feel anything when you read this ?
Markus Schaber wrote: Hi, Eugene, Eugene E. wrote: Okay, now pass that to strcmp or a %s format. AFAIK, the "textual-form" of values is meant to be a c-string. "ab\0cd\0" is not a c-string containing ab\0cd, it's a c-string containing ab. WHY strcmp ?! do you really think the user is a fool ? if the user declared something "binary", he obviously knows what he has done. But when the user requests the canonical _text_ representation of a byte area data type, why do you consider him declaring it "binary"? he did not request this representation. it is _by_default_ if you wish to provide it by request, please do it. i ask you to provide minimal convertion by default, mentioned the user wants his data unchanged. and let the user interpret his own data himself. Since this structure provides a length of each value, you have no need in c-string. Why do think the user needs it ? A user that does not have a need in C-Strings can fetch the binary representation, getting higher efficency for all datatypes. and lose the pretty good representation of all other columns in the same request. "textual-form" is just a name of actually existent convertion rule. i am not trying to find out a philosophy here. There is no philosophy but orthogonality. There's a textual and a binary form of datatypes. For varchar, byta, int4, float, PostGIS geometries etc... good. i ask you to slightly change "textual" representation of bytea. The text representation is pretty useful for human readers for _most_ datatypes, the binary representation is much easier to parse for programs. You are right. but Who said that i can not display something ? i thougth, human-readability of some data depends completely on how CLIENT-SIDE interpret it. server do not know and should not know what data is human readable or printable... etc. So use the binary representation for everything if you don't want to display the data to the user directly. The problem we discuss is not about displaing or printig at all. Some applications want "textual-form" -- most applications but not only to display and in the _same_ query the same applications want bytea... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
