Re: [HACKERS] [BUGS] ON CONFLICT with constraint name doesn't work

[Nikolay Samokhvalov]

This is a kindly reminder, that this problem (message about "constraint"
violation, while there is no such a constraint defined, just an index) is
still unresolved.

Let's fix that naming?

Patch is attached in the previous message (posted to -bugs list)

On Thu, Mar 16, 2017 at 9:15 PM, Nikolay Samokhvalov <samokhva...@gmail.com>
wrote:
>
> Anyway, attached are 2 separate patches:
>  1) version 2 of patch fixing the message, including regression tests;
>  2) proposed change to the documentation https://www.postgresql.org/
> docs/current/static/sql-insert.html
>
>

Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

[Nikolay Samokhvalov]

On Fri, Jul 29, 2016 at 5:28 PM, Jim Nasby  wrote:
>
> The way I sum up MySQL vs PG for people that ask is to recount how they
> "fixed" the Feb. 31st bug when they released strict mode (something that
> they actually called out in the release PR). With strict mode enabled, Feb.
> 30th and 31st would give you an error. Feb 35th was still silently
> converted to March whatever. *That was the MySQL mentality: data quality
> doesn't matter compared to "ease of use".*
>
> They've done this throughout their history... when presented with a hard
> problem, they skip around it or plaster over it, and then they promote that
> their solution is the only right way to solve the problem. (Their docs
> actually used to say that anything other that table-level locking was a bad
> idea.)


This is exactly what I mean saying MySQL speaks different language than I
know, and that's why I simply cannot use it:

(mysql 5.7.12)

mysql> select cast('2016-99-99' as date);
++
| cast('2016-99-99' as date) |
++
| NULL   |
++
1 row in set, 1 warning (0.00 sec)


In Postgres:

test=#  select cast('2016-99-99' as date);
ERROR:  date/time field value out of range: "2016-99-99"
LINE 1: select cast('2016-99-99' as date);
^

Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

[Nikolay Samokhvalov]

On Fri, Jul 29, 2016 at 4:39 AM, Tatsuo Ishii  wrote:
>
> Great translation.
>
> BTW, is there any opposite information, i.e. showing the limitation of
> MySQL comparing with PostgreSQL? I'm not familiar with MySQL, but
> occasionally hearing surprising (as a PostgreSQL user) limitation of
> MySQL and wondering if there's any summary of the info.
>

Sorry cannot help with that :-) I stopped using MySQL in 2005, when
discovered that we speak different languages (I learned standard ISO/ANSI
SQL in university, and then easily communicated with Oracle, SQL Server,
but failed to do so with MySQL; that's why I switched to Postgres).

During last years, all the focus of "let's compare Postres to ..." activity
was switched from MySQL to MongoDB and Oracle.
Maybe it's time to refresh the data -- for those who works with both
Postgres and MySQL.

[HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

[Nikolay Samokhvalov]

Following Uber's case discussion, I found this talk by Alexey Kopytov to be
really interesting:
  http://kaamos.me/talks/pgday16/strongmysql/strongmysql.html (online html,
in Russian)

I translated it to English:

https://www.dropbox.com/s/t6a15s66jxg50tg/mysqlstrong_pgday16russia.pdf?dl=0
(pdf)

The slides deck contains a lot of details. The author claims that during
recent years, MySQL made a lot of progress in defending and advancing its
position as a "most popular database for the web", he provides detailed
reasoning for that, and then concludes that PostgreSQL will need years and
maybe even decades to close gaps in the certain fields which are very
sensitive for large companies:
 - replication
 - storage engines / compression / direct IO / etc
 - partitioning,
etc.

Of course this information is biased (Alexey works at Percona) but IMO it's
much more detailed, qualitative and useful analysis compared to the Uber's
recent article.

[HACKERS] Full (special) logs for specified users/hosts/etc

[Nikolay Samokhvalov]

Hello hackers,

Consider a Postgres cluster containing several DBs (for example several
projects/sites). If one wants to optimize queries on one specified site --
what should he do? His obvious need is to switch full logging for the exact
database on, collect the logs and analyze them using pgFouine/pgBadger.

But currently, there is no way to specify log settings for one database /
one user / one host. There are only *global* settings in postgres.conf.

We experienced this problem in various project several times already.
Usually it is  solved with initdb-ing separate cluster, what is annoying
and difficult if you have replication installed and working.

How difficult is to implement *local* logging settings per
database/user/host? Is it worth to work on for future versions?

[HACKERS] \d failing to find uppercased object names

[Nikolay Samokhvalov]

Hello,

some app created tables ussing uppercase letters in table names (this app
was migrated from mysql). One tries to use \d to search tables info:

ru=# \d pnct_Board
Did not find any relation named pnct_Board.

Of course, it works with :
ru=# \d pnct_Board
 Table public.pnct_Board
   Column|   Type   | Modifiers

-+--+---
 id  | bigint   | not null default
nextval('pnct_Board_id_seq'::regclass)
...


Could this be considered as gotcha?

The thing is that Postgres reply is pretty much clear:
  Did not find any relation named pnct_Board.
, but table pnct_Board does exist in the system, so Postgres' reply is
obviously incorrect.

Version: 9.2.1

Re: [HACKERS] rfc: changing documentation about xpath

[Nikolay Samokhvalov]

On Thu, Jun 3, 2010 at 16:02, Andrew Dunstan and...@dunslane.net wrote:


 Denis I. Polukarov wrote:

  Hi!

 I'm to face a problem, and not at once resolve it.


 [default namespace mapped in xml xmlns= attribute requires corresponding
 mapping in third param of xpath()]

 It's a tolerably subtle point, and I'm not sure it's really
 PostgreSQL-specific. But if you think the docs need improvement, then please
 suggest a patch with the extra wording you think would make things clearer.

  http://www.postgresql.org/mailpref/pgsql-hackers


You are absolutely right, it's not really Postgres-specific, it's XML
specific, but every novice using xpath encounters with this unclear point.
So, small docs patch is sent to -docs.

[HACKERS] Row estimation for var const and for NOT (...) queries

[Nikolay Samokhvalov]

Hi,

I have a table table1 with ~100k rows, the table having flag1
column. The value of flag1 is NULL in 85k+ rows, and it's TRUE in
7k+ rows, and FALSE in 6k rows. I use EXPLAIN to get apprx. number of
rows for simple SELECT queries. But in case of ...WHERE NOT flag1
the optimizer is completely wrong:

 -- it's OK here, the estimation is fine
test=# EXPLAIN ANALYZE SELECT * FROM table1 WHERE flag1;
 QUERY PLAN
-
 Seq Scan on table1  (cost=0.00..9962.84 rows=7875 width=532) (actual
time=0.107..134.729 rows=7652 loops=1)
   Filter: flag1
 Total runtime: 139.460 ms
(3 rows)

 -- here optimizer thinks that we have 90k+ rows with flag1 = FALSE,
while the real number of rows is 6k+
test=# EXPLAIN ANALYZE SELECT * FROM table1 WHERE NOT flag1;
  QUERY PLAN
--
 Seq Scan on table1  (cost=0.00..9962.84 rows=91809 width=532) (actual
time=0.087..110.596 rows=6243 loops=1)
   Filter: (NOT flag1)
 Total runtime: 114.414 ms
(3 rows)

I've checked statistics available and have found that Postgres
actually knows how many FALSE values are present (approximately) in
the table:
test=# SELECT null_frac, n_distinct, most_common_vals,
most_common_freqs FROM pg_stats
WHERE tablename='table1' AND attname='flag1';
 null_frac | n_distinct | most_common_vals | most_common_freqs
---++--+---
  0.864667 |  2 | {t,f}| {0.079,0.056}
(1 row)

So, I've started to think that this is a shortcoming of the optimizer
code, which makes Postgres count both FALSEs and NULLs when estimating
var  const expressions.

1) backend/utils/adt/selfuncs.c, in neqsel() we have:

...
result = DatumGetFloat8(DirectFunctionCall4(eqsel, ...
...
result = 1.0 - result;
PG_RETURN_FLOAT8(result);
...

-- so, there is a wrong assumption that for var  const expressions
we may just use estimation for var = const and subtract it from 1.
In fact, NULLs are ignored here. According to ternary logic, in this
case we must subtract the number of NULLs also. This will improve row
estimation for var  const queries (but not in case when we deal
with boolean datatype, look at (2)!). If there are no objections, I'll
send the patch, which is straightforward.

2). In case of WHERE flag1 = FALSE or WHERE flag1  TRUE the
planner rewrites the query to WHERE NOT flag1 and then uses the
logic defined in backend/optimizer/path/clausesel.c, where, again, we
see the wrong approach which ignores NULLs:

...
else if (not_clause(clause))
{
/* inverse of the selectivity of the underlying clause */
s1 = 1.0 - clause_selectivity(root,
  (Node *) 
get_notclausearg((Expr *) clause),
  
varRelid,
  
jointype);
...
I have no idea how to improve this. AFAIKS, at this level we have no
knowledge about the data we're dealing with (am I right?) -- so, I'm
afraid that for booleans there is no way to improve the optimizer. If
my thoughts described in (1) are correct and we improve the estimation
for , we will have a situation where using booleans might decrease
the performance due to wrong rows count estimation.

I'll appreciate any help and ideas that will allow to improve the situation.

P. S. I use current HEAD version of Postgres; before running queries
the statistic was updated with ANALYZE
-- 
Best regards,
Nikolay

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Multiple SRF right after SELECT

[Nikolay Samokhvalov]

Hi,

I wonder, if the following is correct and provides expected result:

test=# select generate_series(1, 2), generate_series(1, 4);
 generate_series | generate_series
-+-
   1 |   1
   2 |   2
   1 |   3
   2 |   4
(4 rows)


Actually I have two questions on this:
 1. Is it correct at all to use SRF in select list, w/o explicit FROM?
Why then we do not allow using subselects that return multiple rows?
I'd rather expect that these two things work in similar manner.
 2. Why the query above provides 4 rows, not 2*4=8? Actually, that's
interesting -- I can use this query to find l.c.m. But it's defenetely
not that I'd expect before my try...
-- 
Best regards,
Nikolay

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal casting from XML[] to int[], numeric[], text[]

[Nikolay Samokhvalov]

On Sep 25, 2007 10:57 AM, Pavel Stehule [EMAIL PROTECTED] wrote:

 Hello

 Current result from xpath function isn't indexable. It cannot be
 problem with possibility cast it to some base types.

 CREATE OR REPLACE FUNCTION xml_list_to_int(xml[])
 RETURNS int[] AS $$
 SELECT ARRAY(SELECT to_number(($1[i])::text,'99.99')::int
FROM generate_series(1, array_upper($1,1)) g(i))
 $$ LANGUAGE SQL IMMUTABLE;


 CREATE CAST (xml[] AS int[]) WITH FUNCTION xml_list_to_int(xml[]);

 -- now I can build functional index
 CREATE INDEX fx ON foo USING
 GIN((xpath('//id/text()',objednavka_v_xml)::int[]));

 Does anybody know better solution?


Alternative (and maybe better) approach would be:
 - create comparison functions that work in the same way as string
comparison functions do (currently, it's straight forward since XML is
stored as string);
 - do NOT create comparison operators to avoid explicit comparing XML values
(to follow standard ways to work with XML and to avoid possible unexpected
behaviors);
 - create opclass based on these functions and, therefore, obtain GIN
indexes support for xml[];
 - describe in the docs, that one can use GIN indexes over XPath
expressions, but should be aware that comparison with non-trivial XML
constants have to be used carefully because of possible problems with
whitespaces, etc (in other words, comparison here is doing letter by letter,
as for varchar).

If there are no objections I'll send patch for this.

Re: [HACKERS] proposal casting from XML[] to int[], numeric[], text[]

[Nikolay Samokhvalov]

On Nov 12, 2007 12:59 AM, Tom Lane [EMAIL PROTECTED] wrote:
 I'm not clear on what you're proposing.  There is no such thing as an
 opclass with no operators (or at least, not a useful one), so this seems
 mutually contradictory.

 regards, tom lane


You're right, that's my mistake, sorry. So, having casting rules seems
to be the only option..

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] full text search in 8.3

[Nikolay Samokhvalov]

On 10/11/07, Florian G. Pflug [EMAIL PROTECTED] wrote:

 Maybe we could document some regexp, awk script, or similar that strips the
 tsearch stuff from such a table of contents?

Just my .02c for those who will work on migration manual.

In my case, all tsearch2 stuff was kept (before 8.3) in separate
schema, namely tsearch2. So, in 8.2, I had tsearch2.tsvector and
tsearch2.tsquery data types and so on.

During restoration to 8.3 I've catched segfaults -- during INSERTs
into tables with tsearch2.tsvector columns. What helped me is the
following procedure:
  1. restore schema only;
  2. restore data with replacing tsearch2.tsvector datatype to tsvector:
sed -e 's/tsearch2\.tsvector/tsvector/g' DATADUMP | psql DBNAME
2restoration_errors.log
  3. drop tsearch2 schema since it isn't needed anymore.

After that -- everything works normally.
My case is specific since I use separate schemas for every single
contrib module.
-- 
Best regards,
Nikolay

---(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: [HACKERS] full text search in 8.3

[Nikolay Samokhvalov]

On 10/11/07, Tom Lane [EMAIL PROTECTED] wrote:
 Nikolay Samokhvalov [EMAIL PROTECTED] writes:
  During restoration to 8.3 I've catched segfaults -- during INSERTs
  into tables with tsearch2.tsvector columns.

 Segfaults?  That shouldn't happen.  Please show a test case.

Test case: use old tsearch2.so to register all tsearch2 functions to
tsearch2 schema (old fashioned way). Then try:


nikolay=# select '111'::tsearch2.tsvector;
ERROR:  invalid memory alloc request size 1878491112
nikolay=# select '111'::tsearch2.tsvector;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!

Backtrace:

Program received signal SIGSEGV, Segmentation fault.
0x082c2d91 in gettoken_tsvector (state=0xbfae77cc, strval=0x100,
lenval=0x0, pos_ptr=0xbfae7a1c, poslen=0x0, endptr=0x0) at
tsvector_parser.c:209
209 RETURN_TOKEN;
(gdb) bt
#0  0x082c2d91 in gettoken_tsvector (state=0xbfae77cc, strval=0x100,
lenval=0x0, pos_ptr=0xbfae7a1c, poslen=0x0, endptr=0x0) at
tsvector_parser.c:209
#1  0xa730d85f in tsvector_in () from /test/lib/postgresql/tsearch2.so
#2  0x082dda5f in InputFunctionCall (flinfo=0x0, str=0x8479c00 111,
typioparam=61357, typmod=-1) at fmgr.c:1835
#3  0x082dfe26 in OidInputFunctionCall (functionId=61358,
str=0x8479c00 111, typioparam=61357, typmod=-1) at fmgr.c:1939
#4  0x081317a7 in stringTypeDatum (tp=0xa732eef8, string=0x8479c00
111, atttypmod=-1) at parse_type.c:462
#5  0x081343bd in coerce_type (pstate=0x8479bb4, node=0x8479ec0,
inputTypeId=705, targetTypeId=61357, targetTypeMod=-1,
ccontext=COERCION_EXPLICIT,
cformat=COERCE_EXPLICIT_CAST) at parse_coerce.c:210
#6  0x08134b8c in coerce_to_target_type (pstate=0x8479bb4,
expr=0x8479ec0, exprtype=705, targettype=61357, targettypmod=-1,
ccontext=COERCION_EXPLICIT,
cformat=COERCE_EXPLICIT_CAST) at parse_coerce.c:81
#7  0x081279d3 in typecast_expression (pstate=0x8479bb4,
expr=0x8479ec0, typename=0x8479cd4) at parse_expr.c:2221
#8  0x0812872b in transformExpr (pstate=0x8479bb4, expr=0x8479d00) at
parse_expr.c:150
#9  0x081369fc in transformTargetEntry (pstate=0x8479bb4,
node=0x8479d00, expr=0x0, colname=0x0, resjunk=0 '\0') at
parse_target.c:74
#10 0x08136ed4 in transformTargetList (pstate=0x8479bb4,
targetlist=0x8479d5c) at parse_target.c:146
#11 0x0810f188 in transformStmt (pstate=0x8479bb4,
parseTree=0x8479d78) at analyze.c:695
#12 0x0811103f in parse_analyze (parseTree=0x8479d78,
sourceText=0x847939c select '111'::tsearch2.tsvector;,
paramTypes=0x0, numParams=0) at analyze.c:96
#13 0x0822e00e in pg_analyze_and_rewrite (parsetree=0x8479d78,
query_string=0x847939c select '111'::tsearch2.tsvector;,
paramTypes=0x0, numParams=0)
at postgres.c:596
#14 0x0822e1b9 in exec_simple_query (query_string=0x847939c select
'111'::tsearch2.tsvector;) at postgres.c:899
#15 0x0822fde6 in PostgresMain (argc=4, argv=value optimized out,
username=0x841f508 nikolay) at postgres.c:3529
#16 0x081ff938 in ServerLoop () at postmaster.c:3181
#17 0x08200656 in PostmasterMain (argc=3, argv=0x841b878) at postmaster.c:1027
#18 0x081b34c0 in main (argc=3, argv=0xbfae7a4c) at main.c:188


I don't think that it's abnormal, because segfault was caused by old
.so. Nothing wrong, right? But what we should worry about is the fact
that some users will follow the same way I did and will have this
segfault too... Maybe we should notice that one should remove old
tsearch.so during upgrade process (the case when one runs 8.2 and 8.3
on the same machine).

-- 
Best regards,
Nikolay

---(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: [HACKERS] full text search in 8.3

[Nikolay Samokhvalov]

On 10/11/07, Tom Lane [EMAIL PROTECTED] wrote:
 Nikolay Samokhvalov [EMAIL PROTECTED] writes:
  On 10/11/07, Tom Lane [EMAIL PROTECTED] wrote:
  Segfaults?  That shouldn't happen.  Please show a test case.

  Test case: use old tsearch2.so to register all tsearch2 functions to
  tsearch2 schema (old fashioned way). Then try:

 How did you get 8.3 to load the old .so at all?  It should have the
 wrong PG_MODULE_MAGIC values.

Ooops, it's my mistake, sorry. It was CVS version of contrib/tsearch2.
So, users shouldn't encounter with this problem.

-- 
Best regards,
Nikolay

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] mal advice in FAQ 4.1.

[Nikolay Samokhvalov]

Hubert recently posted his thoughts on this topic:
http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

I've encountered with this problem several times in web development and
every time found out that the best (in terms of performance) solution is to
use some pseudo random approach (such as = random() limit 1 or limit 1
offset random()*N or even pre-caching rows on app side).

On 10/9/07, Pavel Stehule [EMAIL PROTECTED] wrote:

 Hello

 I found lot of slow queries in some databases which I checked based on
 advice 4.1. from FAQ,

 To SELECT a random row, use:
 SELECT col
 FROM tab
 ORDER BY random()
 LIMIT 1;

 It's robust and slow on bigger tables. Can we add some better solutions?



-- 
Best regards,
Nikolay

[HACKERS] Unclarity of configure options

[Nikolay Samokhvalov]

The current (CVS version) configure script has the following options
(among many others):

  --enable-dtrace build with DTrace support
  --with-ossp-uuidbuild with OSSP UUID library for UUID generation
  --with-libxml   build with XML support
  --with-libxslt  build with XSLT support

One could think that adding any of this option to ./configure before
building Postgres from sources, he will have corresponding support
after installation and initdb process. But what we have now is the
huge difference between --with-libxml and --with-libxslt: while
the first one adds XML support to the core, the second one doesn't
provide anything automatically, it allows only using contirb/xml2
(what is unclear because the help message is the same as for
--with-libxml -- build with ... support).

Also, comparing --enable-dtrace and --with-libxml I cannot see any
difference in its semantics: --enable-dtrace also depends on external
library and configure process fails if the system doesn't have it. So
why --enable- is used in the first case and --with- in the second
one?

-- 
Best regards,
Nikolay

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] proposal casting from XML[] to int[], numeric[], text[]

[Nikolay Samokhvalov]

The problem with contrib/xml2's xpath_* functions (that return
scalars) was that they are very specific. If XPath expression
evaluation returns array of values (set of XML pieces), but the
function returns only the first, significant information is lost,
while there is no any gain in speed at all.

The key idea was to create only one generic function at the first
stage -- xpath(), returning an array of XML pieces.

We would create wrappers returning int[], bool[], string[], but there
are several issues with such functions:
  - if the type of the data located on nodes that match XPath
expression differs from what is expected, what should we do?
  - in XML world, if you request for a text under some node, all
descendants should be involved in generating result string (example:
what should be returned for XML like emstrongPostgreSQL/strong
is a powerful, open source relational database system/em if user
requests for text under em node? In XML world, the correct answer is
PostgreSQL  is a powerful, open source relational database system --
concatenation of all strings from the node itself and all its
descendants, in the correct order. Will be this expected for RDBMS
users?).

Regarding GIN indexes, alternative approach would be creating opclass
for xml[], it should be pretty simple (and better than creating
implicit CASTs for xml[]-int[], xml[]-bool[], etc). Can we do this
for 8.3 or it's too late? It would be very helpful feature.

Without that, the only way to have indexes is to use functional btree
indexes over XPath expression (smth like ...btree(((xpath('...',
field)[1]::text)) -- pretty ugly construction...)

On 9/25/07, Peter Eisentraut [EMAIL PROTECTED] wrote:
 Am Dienstag, 25. September 2007 schrieb Pavel Stehule:
  Current result from xpath function isn't indexable. It cannot be
  problem with possibility cast it to some base types.

 Nikolay might be able to remind us what happened to the proposed functions
 xpath_bool, xpath_text, etc.

 --
 Peter Eisentraut
 http://developer.postgresql.org/~petere/



-- 
Best regards,
Nikolay

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Getting to 8.3 beta1

[Nikolay Samokhvalov]

On 9/27/07, Tom Lane [EMAIL PROTECTED] wrote:
 * Draft release notes --- can't really ship a beta without these,
 else beta testers won't know what to test.  Traditionally this has
 taken a fair amount of time, but I wonder whether we couldn't use
 http://developer.postgresql.org/index.php/WhatsNew83
 for at least the first cut.

I've modified XML part of wiki page:

XML Support
* This new data type (XML) validates input for well-formedness and
has a set of type-safe operations.
* SQL/XML publishing functions, per SQL:2003
* xpath() function for XPath 1.0 expressions evaluation (with
Namespaces support)
* Alternative XML export function

-- 
Best regards,
Nikolay

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] proposal casting from XML[] to int[], numeric[], text[]

[Nikolay Samokhvalov]

On 9/28/07, Pavel Stehule [EMAIL PROTECTED] wrote:
  We would create wrappers returning int[], bool[], string[], but there
  are several issues with such functions:
- if the type of the data located on nodes that match XPath
  expression differs from what is expected, what should we do?

 raise exception

Will it be convenient for cases when there are many different (various
structures) XMLs in one column (no single DTD)?


- in XML world, if you request for a text under some node, all
  descendants should be involved in generating result string (example:
  what should be returned for XML like emstrongPostgreSQL/strong
  is a powerful, open source relational database system/em if user
  requests for text under em node? In XML world, the correct answer is
  PostgreSQL  is a powerful, open source relational database system --
  concatenation of all strings from the node itself and all its
  descendants, in the correct order. Will be this expected for RDBMS
  users?).

 It is corect. Or we can disallow any nested elements in casting array.
 It's poblem only for text type. Numeric types are clear.

Actually, casting to numeric types might seem to be odd. But there is
some sense from practical point of view -- it works and that's better
that nothing (like now). But it's too late for 8.3, isn't it?


  Regarding GIN indexes, alternative approach would be creating opclass
  for xml[], it should be pretty simple (and better than creating
  implicit CASTs for xml[]-int[], xml[]-bool[], etc). Can we do this
  for 8.3 or it's too late? It would be very helpful feature.

 It's not practic. If I would to use it for functional indexes for
 xpath functions I need constructor for xml[], and I have not it
 currently:

 xpath('/root/id/text()', column)::int[] @ ARRAY[199,2200,222]

I do not understand. Do you mean that there is no equality comparison
operator for type xml yet?

To implement GIN for xml[] we need to have comparison operator for
xml. Standard says XML values are not comparable (subclause 4.2.4 of
the latest draft from wiscorp.com), but without that cannot implement
straight GIN support, what is not good :-/

-- 
Best regards,
Nikolay

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Contrib modules documentation online

[Nikolay Samokhvalov]

There is a problem with line feeds for contrib/xml2:
http://www.nan-tic.com/ftp/pgdoc/xml2.html

As for idea itself, I find it very useful (besides usability
improvements, it would help to promote Postgres advanced features).

On 8/29/07, Albert Cervera i Areny [EMAIL PROTECTED] wrote:
 I've been working on converting the current README files for all contrib
 modules into sgml and add it to the documentation. There are still some fixes
 to do but i'd like to have some feedback. Indeed, it wasn't agreed to have
 all if any of the modules together with the core documentation.

 You can see the docs on [1] in chapter VIII. If you think these could be a
 good addition, please fill free to comment on how you think sections should
 be organized to be consistent and easy to read.

 [1] http://www.nan-tic.com/ftp/pgdoc

 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?

http://archives.postgresql.org



-- 
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] EXPLAIN omits schema?

[Nikolay Samokhvalov]

On 6/13/07, Gregory Stark [EMAIL PROTECTED] wrote:

I'm not a fan either so perhaps I'm biased, but this seems like a good example
of where it would be an *awful* idea.

Once you have an XML plan what can you do with it? All you can do is parse it
into constituent bits and display it.


...and display it -- this, I suppose, covers the most frequent needs
(starting from displaying entire plans in some tools and finishing
with odd but useful examples like
http://archives.postgresql.org/pgsql-sql/2005-08/msg00046.php).


You cant do any sort of comparison
between plans, aggregate results, search for plans matching constraints, etc.


Wrong.


How would I, with XML output, do something like:

SELECT distinct node.relation
  FROM plan_table
 WHERE node.expected_rows  node.actual_rows*2;

or

SELECT node.type, average(node.ms/node.cost)
  FROM plan_table
 GROUP BY node.type;


XPath can help here. Now almost every language has XML with XPath
support. That's the point, that's why XML is suitable here -- it
simplifies application development (in this specific case ;-) ).

--
Best regards,
Nikolay

---(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: [HACKERS] [PATCHES] build/install xml2 when configured with libxml

[Nikolay Samokhvalov]

On 6/2/07, Andrew Dunstan [EMAIL PROTECTED] wrote:

On further consideration I don't see the necessity for this. We don't
say this about lib-ossp-uuid although it too is only used for a contrib
module.


And is it good? For that functionality I would also add comment
describing that this --with... relates to contib only. What we have
now is not absolutely correct situation when user could wrongly think
that (s)he will have capabilities, just adding --with-..., but (s)he
won't.

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [PATCHES] build/install xml2 when configured with libxml

[Nikolay Samokhvalov]

On 4/15/07, Peter Eisentraut [EMAIL PROTECTED] wrote:

Well, if we're going to make libxslt an explicit thing, then it'd be
trivial to add an xslt transformation function into the core, and then
I think we can claim equivalent support.  But we'll have to check the
details, of course.

I have been thinking, however, that I don't want to add more and more
library dependencies into the server.  libxml2 was necessary to some
extent.  But xslt functionality could easily be provided as a module.
This would be easy to do and might be useful even for 8.3.  But I don't
really know how to label that.  Having a contrib/xslt alongside
contrib/xml2 would probably be confusing.  Ideas?


The current CVS' configure is really confusing: it has --with-xslt
option, while there is no XSLT support in the core. At least let's
change the option's comment to smth like build with XSLT support (now
it is used for contrib/xml2 only)...

--
Best regards,
Nikolay

---(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: [HACKERS] [PATCHES] build/install xml2 when configured with libxml

[Nikolay Samokhvalov]

On 5/20/07, Andrew Dunstan [EMAIL PROTECTED] wrote:


contrib is a misnomer at best. When 8.3 branches I intend to propose
that we abandon it altogether, in line with some previous discussions.

We can change the configure help text if people think it matters that
much - which seems to me much more potentially useful than changing
comments.


Actually, I meant configure help text, not any comment in the code :-)

--
Best regards,
Nikolay

---(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: [HACKERS] select ('{}'::text[])[1] returns NULL -- is it correct?

[Nikolay Samokhvalov]

On 4/9/07, Tom Lane [EMAIL PROTECTED] wrote:

Nikolay Samokhvalov [EMAIL PROTECTED] writes:
 As I can see here, when I ask for element that doesn't exist, the
 database returns NULL for me. Maybe it's well-known issue (and
 actually I understood this behaviour before), but strictly speaking it
 seems wrong for me: the database _knows_ that there is no element, so
 why NULL?
[...]

AFAIR it's always been like that, so changing it seems exceedingly
likely to break some peoples' applications.  It's not completely without
analogies in SQL, anyway: consider the behavior of INSERT when fewer
columns are provided than the table has.  Pretending that elements
outside the stored range of the array are null is not all that different
from silently adding nulls to a row-to-be-stored.


OK, I see.
But if I try to INSERT to column that doesn't exist in the table, I
have an error.
Why pg's arrays are designed so that postgres doesn't produce errors
for attempts to access nonexistent element of array? Why there is no
simple sanity check (SELECT (ARRAY[6,8])[-1] -- works w/o an error)? I
remember several cases when people (e.g. me :-) ) were spending some
time trying to find an error in some pl/pgsql function and the reason
lied in incorrect work with arrays (i.e. messages like index is out
of bounds and index cannot be negative number would help, surely).

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] select ('{}'::text[])[1] returns NULL -- is it correct?

[Nikolay Samokhvalov]

On 4/10/07, Tom Lane [EMAIL PROTECTED] wrote:

Nikolay Samokhvalov [EMAIL PROTECTED] writes:
 I remember several cases when people (e.g. me :-) ) were spending some
 time trying to find an error in some pl/pgsql function and the reason
 lied in incorrect work with arrays (i.e. messages like index is out
 of bounds and index cannot be negative number would help, surely).

Well, if indexes *couldn't* be negative numbers then that might be
helpful, but they can.



Ooops :-) OK, my proposal is narrowing to very simple one: what about
triggering WARNINGs when user tries to access nonexistent element of
array?

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] select ('{}'::text[])[1] returns NULL -- is it correct?

[Nikolay Samokhvalov]

Thinking about XPath's output in cases such as 'SELECT xpath('/a', 'b
/');' I've realized that in such cases an empty array should be
returned (now we have NULL for such cases).

Why? Because database _knows_ that there is no element -- this is not
NULL's case (unknown).

Then I've examined how the work with arrays in Postgres is organized.
And now the result of the following query seems to be a little bit
strange for me:

xmltest=# select ('{}'::text[])[1] IS NULL;
?column?
--
t
(1 row)

As I can see here, when I ask for element that doesn't exist, the
database returns NULL for me. Maybe it's well-known issue (and
actually I understood this behaviour before), but strictly speaking it
seems wrong for me: the database _knows_ that there is no element, so
why NULL?

Actually, I do not know what output value would be the best for this
case (and I understand that it'd be very painful to change the
behaviour because of compatibility issues), so my questions are:
1. is it worth to trigger at least notice message (WARNING?) for such cases?
2. what should I do with XPath function? There is strong analogy
between its case and array's case in my mind... Should I leave NULLs,
or empty arrays are better?

BTW, is there any better way to select empty array as a constant
(better then my '{}'::text[])?

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] --enable-xml instead of --with-libxml?

[Nikolay Samokhvalov]

On 4/5/07, Bruce Momjian [EMAIL PROTECTED] wrote:


Nikolay Samokhvalov wrote:
[...]
 If I am wrong and it's better to leave libxml2-free capabilities, then
IMHO
 we need to reflect it explicitly in the docs, what requires libxml2, and
 what doesn't

Agreed, let's do the later and update the documentation.



So, you are agreed that I am wrong :-) Well... I would be happy hear some
arguments, but I cannot insist on it :-)


Also, do we

output a helpful message if someone tries to use a libxml2 function that
isn't available.



Yep, here it is:

INSERT INTO xmltest VALUES (1, 'valueone/value');
ERROR: no XML support in this installation

I suppose we should change it to no libxml2 support in this installation,
shouldn't we?



--
Best regards,
Nikolay

Re: [HACKERS] [PATCHES] xpath_array with namespaces support

[Nikolay Samokhvalov]

On 4/4/07, Peter Eisentraut [EMAIL PROTECTED] wrote:


Am Mittwoch, 4. April 2007 14:43 schrieb Nikolay Samokhvalov:
  Why do we even need to support xpath on fragments?

 Why not? I find it useful and convenient.

Well, rather than inventing bogus root wrapper elements, why not let users
call xmlelement() to produce the wrapper element themselves?



User may even don't know in what case wrapper element is needed. I mean, if
user works with XML column containing both documents and fragments, then
what must he do? Add wrapper anyway? So, users will add XMLELEMENT in almost
any case.

I'd prefer to keep external interfaces simpler (less thinking in such cases
for users).

--
Best regards,
Nikolay

Re: [HACKERS] --enable-xml instead of --with-libxml?

[Nikolay Samokhvalov]

On 3/27/07, Bruce Momjian [EMAIL PROTECTED] wrote:



Where are we on this?  Peter thought the consistency makes sense, but if
we can provide functionality that doesn't require libxml, why not
do it?



I'm still for --enable-xml and putting _everything_ XML-related under this
option. My main points are:
- we cannot guarantee that existing libxml2-free functions will not require
them in the future (because libxml2 contains useful routines);
- libxml2-free functions (e.g. Peter's XML mapping functions,
http://momjian.us/main/writings/pgsql/sgml/functions-xml.html#FUNCTIONS-XML-MAPPING)
produce XML values, but we cannot use XPath function for them unless we have
libxml2;
- people will make errors, trying to understand what needs libxml2, and
what doesn't -- approach all or nothing is simple and straightforward;


Well, it seems that I have no more arguments :-) If there is no objections,
I'll send the patch tonight.
If I am wrong and it's better to leave libxml2-free capabilities, then IMHO
we need to reflect it explicitly in the docs, what requires libxml2, and
what doesn't

--
Best regards,
Nikolay

Re: [HACKERS] [PATCHES] xpath_array with namespaces support

[Nikolay Samokhvalov]

On 3/23/07, Peter Eisentraut [EMAIL PROTECTED] wrote:


Andrew Dunstan wrote:
 Would it be better to use some more unlikely name for the dummy root
 element used to process fragments than x ?

Why do we even need to support xpath on fragments?



Why not? I find it useful and convenient.

--
Best regards,
Nikolay

Re: [HACKERS] [PATCHES] xpath_array with namespaces support

[Nikolay Samokhvalov]

On 3/5/07, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:

On 3/4/07, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:
 I'll fix these issues and extend the patch with resgression tests and
 docs for xpath_array(). I'll resubmit it very soon.

Here is a new version of the patch. I didn't change any part of docs yet.
Since there were no objections I've changed the name of the function
to xmlpath().


Updated version of the patch contains bugfix: there were a problem
with path queries that pointed to elements (cases when a set of
document parts that correspond to subtrees should be returned).
Example is (included in regression test):

xmltest=# SELECT xmlpath('//b', 'aone btwo/b three betc/b/a');
xmlpath
-
{btwo/b,betc/b}
(1 row)

Waiting for more feedback, please check it.

--
Best regards,
Nikolay
Index: src/backend/utils/adt/xml.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/xml.c,v
retrieving revision 1.35
diff -u -r1.35 xml.c
--- src/backend/utils/adt/xml.c	15 Mar 2007 23:12:06 -	1.35
+++ src/backend/utils/adt/xml.c	18 Mar 2007 13:32:21 -
@@ -47,6 +47,8 @@
 #include libxml/uri.h
 #include libxml/xmlerror.h
 #include libxml/xmlwriter.h
+#include libxml/xpath.h
+#include libxml/xpathInternals.h
 #endif /* USE_LIBXML */
 
 #include catalog/namespace.h
@@ -67,6 +69,7 @@
 #include utils/datetime.h
 #include utils/lsyscache.h
 #include utils/memutils.h
+#include access/tupmacs.h
 #include utils/xml.h
 
 
@@ -88,6 +91,7 @@
 static int		parse_xml_decl(const xmlChar *str, size_t *lenp, xmlChar **version, xmlChar **encoding, int *standalone);
 static bool		print_xml_decl(StringInfo buf, const xmlChar *version, pg_enc encoding, int standalone);
 static xmlDocPtr xml_parse(text *data, XmlOptionType xmloption_arg, bool preserve_whitespace, xmlChar *encoding);
+static text		*xml_xmlnodetoxmltype(xmlNodePtr cur);
 
 #endif /* USE_LIBXML */
 
@@ -1463,7 +1467,6 @@
 	return buf.data;
 }
 
-
 /*
  * Map SQL value to XML value; see SQL/XML:2003 section 9.16.
  */
@@ -2403,3 +2406,258 @@
 	else
 		appendStringInfoString(result, /row\n\n);
 }
+
+
+/*
+ * XPath related functions
+ */
+
+#ifdef USE_LIBXML
+/* 
+ * Convert XML node to text (dump subtree in case of element, return value otherwise)
+ */
+text *
+xml_xmlnodetoxmltype(xmlNodePtr cur)
+{
+	xmlChar			*str;
+	xmltype*result;
+	size_tlen;
+	xmlBufferPtr 		buf;
+	
+	if (cur-type == XML_ELEMENT_NODE)
+	{
+		buf = xmlBufferCreate();
+		xmlNodeDump(buf, NULL, cur, 0, 1);
+		result = xmlBuffer_to_xmltype(buf);
+		xmlBufferFree(buf);
+	}
+	else
+	{
+		str = xmlXPathCastNodeToString(cur);
+		len = strlen((char *) str);
+		result = (text *) palloc(len + VARHDRSZ);
+		SET_VARSIZE(result, len + VARHDRSZ);
+		memcpy(VARDATA(result), str, len);
+	}
+	
+	return result;
+}
+#endif
+
+/*
+ * Evaluate XPath expression and return array of XML values.
+ * As we have no support of XQuery sequences yet, this functions seems
+ * to be the most useful one (array of XML functions plays a role of
+ * some kind of substritution for XQuery sequences).
+
+ * Workaround here: we parse XML data in different way to allow XPath for
+ * fragments (see XPath for fragment TODO comment inside).
+ */
+Datum
+xmlpath(PG_FUNCTION_ARGS)
+{
+#ifdef USE_LIBXML
+	ArrayBuildState		*astate = NULL;
+	xmlParserCtxtPtr	ctxt = NULL;
+	xmlDocPtr			doc = NULL;
+	xmlXPathContextPtr	xpathctx = NULL;
+	xmlXPathCompExprPtr	xpathcomp = NULL;
+	xmlXPathObjectPtr	xpathobj = NULL;
+	int32len, xpath_len;
+	xmlChar*string, *xpath_expr;
+	boolres_is_null = FALSE;
+	int	i;
+	xmltype*data;
+	text*xpath_expr_text;
+	ArrayType			*namespaces;
+	int	*dims, ndims, ns_count = 0, bitmask = 1;
+	char*ptr;
+	bits8*bitmap;
+	char**ns_names = NULL, **ns_uris = NULL;
+	int16typlen;
+	booltypbyval;
+	chartypalign;
+	
+	/* the function is not strict, we must check first two args */
+	if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+		PG_RETURN_NULL();
+	
+	xpath_expr_text = PG_GETARG_TEXT_P(0);
+	data  = PG_GETARG_XML_P(1);
+	
+	/* Namespace mappings passed as text[].
+	 * Assume that 2-dimensional array has been passed, 
+	 * the 1st subarray is array of names, the 2nd -- array of URIs,
+	 * example: ARRAY[ARRAY['myns', 'myns2'], ARRAY['http://example.com', 'http://example2.com']]. 
+	 */
+	if (!PG_ARGISNULL(2))
+	{
+		namespaces = PG_GETARG_ARRAYTYPE_P(2);
+		ndims = ARR_NDIM(namespaces);
+		dims = ARR_DIMS(namespaces);
+		
+		/* Sanity check */
+		if (ndims != 2)
+			ereport(ERROR, (errmsg(invalid array passed for namespace mappings),
+			errdetail(Only 2-dimensional array may be used for namespace mappings.)));
+		
+		Assert(ARR_ELEMTYPE(namespaces) == TEXTOID);
+		
+		ns_count = ArrayGetNItems(ndims, dims) / 2;
+		get_typlenbyvalalign(ARR_ELEMTYPE(namespaces),
+			 typlen, typbyval, typalign);
+		ns_names = (char **) palloc(ns_count * sizeof(char *));
+		ns_uris

Re: [HACKERS] [PATCHES] xpath_array with namespaces support

[Nikolay Samokhvalov]

On 3/17/07, Andrew Dunstan [EMAIL PROTECTED] wrote:

In principle I am in favor of the patch.

Would it be better to use some more unlikely name for the dummy root
element used to process fragments than x ?

Perhaps even something in a special namespace?



I did think about it, but I didn't find any difficulties with simple
x.../x. The thing is that regardless the element name we have
corresponding shift in XPath epression -- so, there cannot be any
problem from my point of view... But maybe I don't see something and
it's better to avoid _possible_ problem. It depends on PostgreSQL code
style itself -- what is the best approach in such cases? To avoid
unknown possible difficulties or to be clear?

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [PATCHES] xpath_array with namespaces support

[Nikolay Samokhvalov]

What about it? W/o this not large patch XML functionality in 8.3 will be weak...
Will it be accepted?

On 3/5/07, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:

On 3/4/07, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:
 I'll fix these issues and extend the patch with resgression tests and
 docs for xpath_array(). I'll resubmit it very soon.

Here is a new version of the patch. I didn't change any part of docs yet.
Since there were no objections I've changed the name of the function
to xmlpath().




--
Best regards,
Nikolay
Index: src/backend/utils/adt/xml.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/xml.c,v
retrieving revision 1.34
diff -u -r1.34 xml.c
--- src/backend/utils/adt/xml.c	3 Mar 2007 19:32:55 -	1.34
+++ src/backend/utils/adt/xml.c	5 Mar 2007 01:14:57 -
@@ -47,6 +47,8 @@
 #include libxml/uri.h
 #include libxml/xmlerror.h
 #include libxml/xmlwriter.h
+#include libxml/xpath.h
+#include libxml/xpathInternals.h
 #endif /* USE_LIBXML */
 
 #include catalog/namespace.h
@@ -67,6 +69,7 @@
 #include utils/datetime.h
 #include utils/lsyscache.h
 #include utils/memutils.h
+#include access/tupmacs.h
 #include utils/xml.h
 
 
@@ -88,6 +91,7 @@
 static int		parse_xml_decl(const xmlChar *str, size_t *lenp, xmlChar **version, xmlChar **encoding, int *standalone);
 static bool		print_xml_decl(StringInfo buf, const xmlChar *version, pg_enc encoding, int standalone);
 static xmlDocPtr xml_parse(text *data, XmlOptionType xmloption_arg, bool preserve_whitespace, xmlChar *encoding);
+static text		*xml_xmlnodetotext(xmlNodePtr cur);
 
 #endif /* USE_LIBXML */
 
@@ -1463,7 +1467,6 @@
 	return buf.data;
 }
 
-
 /*
  * Map SQL value to XML value; see SQL/XML:2003 section 9.16.
  */
@@ -2403,3 +2406,247 @@
 	else
 		appendStringInfoString(result, /row\n\n);
 }
+
+
+/*
+ * XPath related functions
+ */
+
+#ifdef USE_LIBXML
+/* 
+ * Convert XML node to text (return value only, it's not dumping)
+ */
+text *
+xml_xmlnodetotext(xmlNodePtr cur)
+{
+	xmlChar		*str;
+	text			*result;
+	size_t			len;	
+	
+	str = xmlXPathCastNodeToString(cur);
+	len = strlen((char *) str);
+	result = (text *) palloc(len + VARHDRSZ);
+	SET_VARSIZE(result, len + VARHDRSZ);
+	memcpy(VARDATA(result), str, len);
+	
+	return result;
+}
+#endif
+
+/*
+ * Evaluate XPath expression and return array of XML values.
+ * As we have no support of XQuery sequences yet, this functions seems
+ * to be the most useful one (array of XML functions plays a role of
+ * some kind of substritution for XQuery sequences).
+
+ * Workaround here: we parse XML data in different way to allow XPath for
+ * fragments (see XPath for fragment TODO comment inside).
+ */
+Datum
+xmlpath(PG_FUNCTION_ARGS)
+{
+#ifdef USE_LIBXML
+	ArrayBuildState		*astate = NULL;
+	xmlParserCtxtPtr	ctxt = NULL;
+	xmlDocPtr			doc = NULL;
+	xmlXPathContextPtr	xpathctx = NULL;
+	xmlXPathCompExprPtr	xpathcomp = NULL;
+	xmlXPathObjectPtr	xpathobj = NULL;
+	int32len, xpath_len;
+	xmlChar*string, *xpath_expr;
+	boolres_is_null = FALSE;
+	int	i;
+	xmltype*data;
+	text*xpath_expr_text;
+	ArrayType			*namespaces;
+	int	*dims, ndims, ns_count = 0, bitmask = 1;
+	char*ptr;
+	bits8*bitmap;
+	char**ns_names = NULL, **ns_uris = NULL;
+	int16typlen;
+	booltypbyval;
+	chartypalign;
+	
+	/* the function is not strict, we must check first two args */
+	if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+		PG_RETURN_NULL();
+	
+	xpath_expr_text = PG_GETARG_TEXT_P(0);
+	data  = PG_GETARG_XML_P(1);
+	
+	/* Namespace mappings passed as text[].
+	 * Assume that 2-dimensional array has been passed, 
+	 * the 1st subarray is array of names, the 2nd -- array of URIs,
+	 * example: ARRAY[ARRAY['myns', 'myns2'], ARRAY['http://example.com', 'http://example2.com']]. 
+	 */
+	if (!PG_ARGISNULL(2))
+	{
+		namespaces = PG_GETARG_ARRAYTYPE_P(2);
+		ndims = ARR_NDIM(namespaces);
+		dims = ARR_DIMS(namespaces);
+		
+		/* Sanity check */
+		if (ndims != 2)
+			ereport(ERROR, (errmsg(invalid array passed for namespace mappings),
+			errdetail(Only 2-dimensional array may be used for namespace mappings.)));
+		
+		Assert(ARR_ELEMTYPE(namespaces) == TEXTOID);
+		
+		ns_count = ArrayGetNItems(ndims, dims) / 2;
+		get_typlenbyvalalign(ARR_ELEMTYPE(namespaces),
+			 typlen, typbyval, typalign);
+		ns_names = (char **) palloc(ns_count * sizeof(char *));
+		ns_uris = (char **) palloc(ns_count * sizeof(char *));
+		ptr = ARR_DATA_PTR(namespaces);
+		bitmap = ARR_NULLBITMAP(namespaces);
+		bitmask = 1;
+		
+		for (i = 0; i  ns_count * 2; i++)
+		{
+			if (bitmap  (*bitmap  bitmask) == 0)
+ereport(ERROR, (errmsg(neither namespace nor URI may be NULL))); /* TODO: better message */
+			else
+			{
+if (i  ns_count)
+	ns_names[i] = DatumGetCString(DirectFunctionCall1(textout,
+		  PointerGetDatum(ptr)));
+else
+	ns_uris[i - ns_count] = DatumGetCString(DirectFunctionCall1(textout

Re: [HACKERS] [PATCHES] xml2 contrib patch supporting default XML namespaces

[Nikolay Samokhvalov]

On 3/6/07, Mike Rylander [EMAIL PROTECTED] wrote:

Attatched you'll find a patch that I've been kicking around for a
while that I'd like to propose for inclusion in 8.3.  I attempted to
submit this through the original xml2 author (as far back as the  7.4
days) but got no response.

It's really fairly trivial, but I will be using the features it
provides in production soon, so I'd like to see it applied against the
contrib xml2 module.  The patch adds support for default XML
namespaces in xml2 by providing a mechanism for supplying a prefix to
a named namespace URI.  It then wraps the namespace-capable functions
in backward-compatible equivalents so that old code will not break.


1) And what about non-default namespaces?
2) What if my XPath query has different prefix, that also should be
mapped to the same URI? (Not frequent case, but this really can occur
-- e.g. XML doc has prefix 'local' for URI='http://127.0.0.1', but
XPath should have 'loc' for the same URI.)

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] Re: [HACKERS] XQuery or XPathサポート

[Nikolay Samokhvalov]

On 3/5/07, Tatsuo Ishii [EMAIL PROTECTED] wrote:

Is there any plan for supporting XQuery or XPath in 8.3?


I've submitted patch for simple XPath 1.0 support (based on libxml2):
http://archives.postgresql.org/pgsql-patches/2007-03/msg00088.php

This function does XML parsing at query time. So, you should use
functional indexes over this function to reach good perfromance.

My curent plans include development of additional contrib module for
alternative (experimental), efficient support of XPath (no XML parsing
at query time), similar to Microsoft's ORDPATHs. This project is
called xlabel and I hope to implement its prototype soon.

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] Re: [HACKERS] Re: [HACKERS] XQuery or XPathサポート

[Nikolay Samokhvalov]

On 3/5/07, Tatsuo Ishii [EMAIL PROTECTED] wrote:

From: Nikolay Samokhvalov [EMAIL PROTECTED]
 I've submitted patch for simple XPath 1.0 support (based on libxml2):
 http://archives.postgresql.org/pgsql-patches/2007-03/msg00088.php

But contrib/README.xml2 stated:

This version of the XML functions provides both XPath querying and
XSLT functionality

What's the difference between yours and contrib/xml2?


Mine is supposed to go to the core. And, contrib/xml2 contains less
semantically clear functions, that were developed for practical
purposes (e.g. xml_number() returns only one, first number from the
suquence).



 This function does XML parsing at query time. So, you should use
 functional indexes over this function to reach good perfromance.

 My curent plans include development of additional contrib module for
 alternative (experimental), efficient support of XPath (no XML parsing
 at query time), similar to Microsoft's ORDPATHs. This project is
 called xlabel and I hope to implement its prototype soon.

Also Peter seems to add xml datatype to 8.3. Does your functuion
handle this data type?


Of course. Peter's patches are partially based on the result of my
SoC2006 work (Initial XML support for PostgreSQL). XPath patch was
discussed with him and in -hackers, now I'm waiting for its review.

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[HACKERS] Re: [HACKERS] Re: [HACKERS] XQuery or XPathサポート

[Nikolay Samokhvalov]

On 3/5/07, Tatsuo Ishii [EMAIL PROTECTED] wrote:

The XPath support is 1.0 or 2.0?


1.0

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] --enable-xml instead of --with-libxml?

[Nikolay Samokhvalov]

On 2/21/07, Alvaro Herrera [EMAIL PROTECTED] wrote:

I think it would be better that leaving --with-libxml out (i.e.
compiling without libxml2 support) would only disable those parts in XML
functionality that require libxml2 for their implementation; the rest of
the stuff should be compiled in regardless of the setting.

Is this not what is done currently?



The thing is that some functions of XML support are based on
libxml2, some are not. libxml2 contains useful routines to deal with
XML data. Now we have: XMLELEMENT uses such routines and XMLPI
doesn't. Actually, all SQL/XML publishing function could be
implemented w/o libxml2 -- but it's more convenient to use those
routines in some cases... And there is no guarantee that functions
that don't currently use libxml2 will not use them in future.

What I want to propose is just simplification -- consider all XML
stuff as one package, including XML type, SQL/XML publishing, XPath
funcs, additional publishing functions recently added by Peter (btw,
who knows -- maybe libxml2 will help to improve them somehow in
future?), etc.

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

[HACKERS] --enable-xml instead of --with-libxml?

[Nikolay Samokhvalov]

Now we have --with-libxml (and USE_LIBXML constant for #ifdef-s), what
is not absolutely right: XML support is smth that is more general than
using libxml2 library. E.g., some SQL/XML publishing functions (such
as XMLPI) do not deal with libxml2.

Also, in the future more non-libxml functionality could be added to
XML support (well, Peter's recent SQL-to-XML mapping functions prove
it).

I think it'd better to rename configure option to --enable-xml and
USE_LIBXML to ENABLE_XML. I'll do it if there are no objections.

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Modifying and solidifying contrib

[Nikolay Samokhvalov]

On 2/6/07, Andrew Dunstan [EMAIL PROTECTED] wrote:

If the extension installs everything in dedicated namespace(s), I think
we would want to have an option to add those namespaces easily to search
paths. Right now all we can do is to set a search path. It would be
nice, for example, to have support for appending or prepending something
to the search path.

I suspect most apps/extensions don't currently use namespaces much, or
we might well have seen more demand in this area.


I still do not understand why is it so needed.
Your argument is some apps aren't able to call functions as
schemaname.functionname(arg1, arg2, ..), right? First of all, I do
not think that the number of such apps is huge. Second, this is really
the problem of those apps themselves.

I still think that separate namespaces for extensions is a good idea
while adjusting search_path is not. I've explained my POV in details
several messages ago in this thread...

Separation of extensions with fully specified names
schemaname.functionname(...) is good improvement (for simplification
and clarity) and  while adjusting search_path should be DBA/DBD's
decision.
--
Best regards,
Nikolay

---(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: [HACKERS] Modifying and solidifying contrib

[Nikolay Samokhvalov]

On 2/7/07, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:

I still think that separate namespaces for extensions is a good idea
while adjusting search_path is not. I've explained my POV in details
several messages ago in this thread...

Separation of extensions with fully specified names
schemaname.functionname(...) is good improvement (for simplification
and clarity) and  while adjusting search_path should be DBA/DBD's
decision.


Oh, I've just recalled the problem that could arise in this scenario...
We cannot use schema name as prefix for operator calling
(tsearch2.ts_debug(...) works, while ... tsearch2.@@ ... doesn't).
This is one specific issue, maybe it's worth to resolve it? Or it's
impossible for some reasons...

--
Best regards,
Nikolay

---(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: [HACKERS] Modifying and solidifying contrib

[Nikolay Samokhvalov]

On 2/7/07, Andrew Dunstan [EMAIL PROTECTED] wrote:

Nikolay Samokhvalov wrote:

 I still do not understand why is it so needed.
 Your argument is some apps aren't able to call functions as
 schemaname.functionname(arg1, arg2, ..), right?

wrong.


 I still think that separate namespaces for extensions is a good idea
 while adjusting search_path is not. I've explained my POV in details
 several messages ago in this thread...


The difference between us is that I am less inclined to be prescriptive
about such matters than you are. I think that as namespace use expands
we should also probably provide better support for adding things to the
search path (or indeed taking things away). If you don't want to use it
then don't, but I don't see why you are so insistent on denying such
facilities to others.


ok, looks like I've misunderstood your mesages. Sorry for that. Surely
additional capabilities for manipulation with search_path cannot
hinder anybody.

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Modifying and solidifying contrib

[Nikolay Samokhvalov]

On 2/5/07, Andrew Dunstan [EMAIL PROTECTED] wrote:
[...]

I would suggest we start with what is (I think) simplest and clearest:

. catalog support via a simple extension-schema(s) map
. initdb installs standard extensions if it finds them, unless told not to
. support for adjusting search path.


Why adjusting search_path is needed at all?

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [HACKERS] Modifying and solidifying contrib

[Nikolay Samokhvalov]

On 1/30/07, Andrew Dunstan [EMAIL PROTECTED] wrote:


[...]
4. visibility/searchpath issues. I don't think long search paths are a
huge issue, but I think we can make life a bit easier by tweaking
searchpath support a bit (David's clever SQL notwithstanding).



As for search_path -- is it really needed to change it? I think it'd be
better to leave default search_path even if we have many extensions each
sitting in its own schema. If DBA/DBD wants, he can change it himself.

The reasons to follow this way are:
 1. two or more extensions might have functions with the same name
(actually, that's what schemes/namespaces serve for) = we do not know which
function should have higher priority (what order for schemas to choose?);
 2. originally, when I've proposed to use separate schema name for each
contrib module I've forgotten to mention another cause to do it -- this
helps in development because everyone always knows what function is used
(the code becomes a little bit larger, but understanding and code
readability are improved) = so, it's better to not tweak search_path, it's
better to encourage DBD to use full function names (if he wants to avoid
using schema names, he can set search_path himself, resolving possible names
priority issues mentioned above).

Finally, AFAIK other DBMSs use the similar approaches (provide additional
extensions/packages/extensions/... using separate namespaces and do not try
to avoid writing namespace in function calls).

--
Best regards,
Nikolay

Re: [HACKERS] XML type and XPath

[Nikolay Samokhvalov]

BTW,

Moreover, I would like xpath_string() which return

On 1/29/07, Peter Eisentraut [EMAIL PROTECTED] wrote:
[...]


So, while I realize that I've been arguing for a lean core recently, I
want to propose that we add a small set of XPath support functions to
the core.  This would come down to approximately the following set

xpath_boolean(query, xml)
xpath_number(query, xml)
xpath_string(query, xml)
xpath_nodeset(query, xml) -- API and return type still unclear


As for the latest one, I am for xml[] as a result type, especially if
we have xpath* in contrib. This is not XQuery sequences, but at least
it allows user to see all XML fragments (and manage them somehow -- if
he wants, he would concatenate them to one value using corresponding
function).

As for #1-3 -- they are very simple things; I do not like them,
because they return only one scalar value, which is the one
encountered first. I do not think it's very useful functions at all...
Moreover, in case of xpath_string() I think it should work in the
following manner:
 1. Find all nodes that correspond the expression given. In general
case it will be a set of nodes; OK, let's take only the first one, as
we do with other functions...
 2. For this node retrieve all text nodes that are its descendant. It
will be an ordered set of text values.
 3. Concatenate all these values and return as a single string.
I suppose, only such behaviour is in compliance with XML data model --
as an example, consider following XML fragment: 'amost
badvanced/b open source database/a'.

So, for xpath_string() I see two issues -- 1) a lack of usability if
it returns only one (the first) value from possible sequences of
values; 2) bad conformance if it take only one text node which belongs
to the first context node.

BTW, maybe it would be useful to have several functions, with every
behaviour that can be useful.

Also, I think it'd be better not to use the word query speaking of
XPath, XPath expression is much better (to avoid confusion with XML
Query).


We also have prospects that later on we might get fancy GIN-based
indexing support for XPath, which might need another xpath_matches()
function or operator of some kind.


Now I'm trying to collect all thought regarding indexes and express it
in a short message (what types of queries should be considered; what
types of indexes would support that queries).

BTW, Do not forget that some type of index is already available - it's
simply functional indexes on xpath_*() with static (i.e. known as a
constant value a priori) XPath expression.


As far as contrib/xml2 is concerned, I'm not going to make any efforts
to make the interface compatible because that module has a rather
pragmatic design, whereas I'd rather just provide the raw operations
that can be assembled easily by the user to achieve some of the things
that contrib/xml2 does now.  Once some description of transition steps
has been developed, I'd deprecate the contrib/xml2 module and probably
remove it after 8.3.

In the wiki we have collected some random ideas of other interesting
operations on XML types
(http://developer.postgresql.org/index.php/XML_Todo, near the bottom).
That list at the moment says:

DTD validation
Relax-NG
XSLT
XML Canonical (to compare XML values)
Pretty-printing XML (e.g., indenting)


I've added Shredding with annotated schemas to this list (with brief
description why it could be needed).

Also, in a long term I see such items as
 - integration/support in pl/perl and other pl-langs that can work with XML;
 - work with web services (maybe it'd better to use pl/perl here).
Maybe it too early to add such things even to the bottom of Todo list :-)

--
Best regards,
Nikolay

---(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

[HACKERS] Proposal: allow installation of any contrib module simultaneously with Postgres itself

[Nikolay Samokhvalov]

Discussion tsearch in core patch, for inclusion  shows
(http://archives.postgresql.org/pgsql-hackers/2007-01/msg01165.php and
following http://archives.postgresql.org/pgsql-hackers/2007-01/msg01186.php)
that there are some problems with contrib promotion and expansion.
I've encountered with bad awareness and some kind of fears (due to
possible security holes and similar reasons) regarding contrib
modules. For example, hstore is very good thing that helps to resolve
many issues (e.g. logging tasks), but not many people know are aware
of it, and there are very few hosting providers which include hstore
to Postgres installation.

So, it would be really good if documentation and the main website
itself include more information describing the modules (maybe to
review README files and include them all in the docs?).

But I want to propose something more. It's clear that some ISPs are
afraid of contrib modules installation, many of which are very useful
and have reliable code. But, those ISPs are not afraid to install,
say, PHP with a dozen modules (extensions). Why? Besides the fact that
PHP modules are very good described in the main PHP manual, I see very
simple reason: to install a contrib module you must go to contrib dir
and run _another_ make install (wth following psql ..  module.sql
surely), while to install PHP extension you should only add
--with-modulename to the configuration command.

Well, my proposal is simple:

 1. Change default behaviour of MODULE_NAME.sql file so it will be
installed in MODULE_NAME schema instead of public (e.g., hstore
schema will contain all hstore relations and functions).
 2. Allow running configure with --with-MODULE_NAME (or
--enable-MODULE_NAME) to include compilation of module's libraries
simultaneously with Postgres itself and including running of module's
registration SQLs (from that .sql files) simultaneously with cluster
creation (in other words, with inidb invocation -- this will add
MODULE_NAME schema to template0).

This will simplify the procedure of starting to use contrib modules
and will help to promote the modules themselves (and, as a result,
some PostgreSQL's advanced features). I think many projects have
similar behaviour with regard to their extensions. And ISPs will
install PostgreSQL with a bundle of useful and trusted extensions,
simply running ./configure --with-tsearch2 --with-hstore
--with-dblink (actually, I hope that tsearch2 will be in core, but
this is really good example at the moment ;-) ) - like they do with
PHP, Apache and other software.

Let's make the usage of contrib modules more user-friendly.

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] tsearch in core patch, for inclusion

[Nikolay Samokhvalov]

On 1/25/07, Teodor Sigaev [EMAIL PROTECTED] wrote:

It's should clear enough for now - dump data from old db and load into new one.
But dump should be without any contrib/tsearch2 related functions.


Upgrading from 8.1.x to 8.2.x was not tivial because of very trivial
change in API (actually not really API but the content of pg_ts_*
tables): russian snowball stemming function was forked to 2 different
ones, for koi8 and utf8 encodings. So, as I  dumped my pg_ts_* tables
data (to keep my tsearch2 settings), I saw errors during restoration
(btw, why didn't you keep old russian stemmer function name as a
synonym to koi8 variant?) -- so, I had to change my dump file
manually, because I didn't manage to follow tsearch2 best practices
(to use some kind of bootstrap script that creates tsearch2
configuration you need from default one -- using several INSERTs and
UPDATEs). And there were no upgrade notes for tsearch2.

So, I consider upgrading process for tsearch2 to be a little bit
tricky till present. I assume it will be improved with 8.3...

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] xml type and encodings

[Nikolay Samokhvalov]

On 1/15/07, Peter Eisentraut [EMAIL PROTECTED] wrote:


Client encoding is A, server encoding is B.  Client sends an xml datum
that looks like this:

INSERT INTO table VALUES (xmlparse(document '?xml version=1.0
encoding=C?content.../content'));

Assuming that A, B, and C are all distinct, this could fail at a number
of places.

I suggest that we make the system ignore all encoding declarations in
xml data.  That is, in the above example, the string would actually
have to be encoded in client encoding B on the client, would be
converted to A on the server and stored as such.  As far as I can tell,
this is easily implemented and allowed by the XML standard.



In other words, in case when B != C server must trigger an error, right?

--
Best regards,
Nikolay

Re: [HACKERS] contrib/xml2 and xml type

[Nikolay Samokhvalov]

Duplicate versions of functions (e.g., there would be XMLPATH() as the main
XPath function for XML type, producing arrays of values of XML type in
general case -- non-standard, but generalized).

In addition to two SQL files for registration of module functions in
database, I would move XSLT functions to separate SQL file (many people do
not need XSLT, just XPath, or vice versa). Also, maybe it's worth to adjust
Makefile to make using of contrib/xml2 without XSLT (on systems w/o libxslt)
a little bit simpler (now everyone have to edit both Makefile and ...sql.in
manually).

On 1/11/07, Peter Eisentraut [EMAIL PROTECTED] wrote:


Assuming a working xml type, what do you think the future of the
contrib/xml2 module should be?

At the moment, I'd imagine that we add duplicate versions of most
functions, where appropriate, that use the xml type instead of the text
type.  Perhaps we should supply two sets of SQL files, so that users
have the choice of using the legacy versions or the type-safe
versions.  Anything else?

(I understand that some people are researching GIN-optimized XPath
access to XML data, but that is really a bit further out.)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq





--
Best regards,
Nikolay

Re: [HACKERS] contrib/xml2 and xml type

[Nikolay Samokhvalov]

On 1/11/07, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:


Duplicate versions of functions (e.g., there would be XMLPATH() as the
main XPath function for XML type, producing arrays of values of XML type in
general case -- non-standard, but generalized).



Sorry :-) I wanted to say I suppose that duplicate functions is a good idea.

--
Best regards,
Nikolay

[HACKERS] How to gain R/W access to developers wiki?

[Nikolay Samokhvalov]

I want to collect all XML todo issues (from my list, from Peter's and
those Tom just described in Loose ends in PG XML patch) in one
place, to make the overall picture clear for everyone.
How can I gain access to developers wiki? (Actually, I've created the
pageXML Todo - http://developer.postgresql.org/index.php/XML_Todo,
but it's locked for editing).

--
Best regards,
Nikolay

---(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: [HACKERS] How to gain R/W access to developers wiki?

[Nikolay Samokhvalov]

Surely, I've created one (nickname: Nikolay) and are trying to do
things being logged in :-)

On 12/24/06, Magnus Hagander [EMAIL PROTECTED] wrote:

Nikolay Samokhvalov wrote:
 I want to collect all XML todo issues (from my list, from Peter's and
 those Tom just described in Loose ends in PG XML patch) in one
 place, to make the overall picture clear for everyone.
 How can I gain access to developers wiki? (Actually, I've created the
 pageXML Todo - http://developer.postgresql.org/index.php/XML_Todo,
 but it's locked for editing).

Not really knowing the internals of how the wiki is set up, I believe
you just have to create an account and sign in with it to get write access.

(Actually, I don't think you've created the page, I think you're just
seeing the default placeholder page for something that's not created.
But again, I don't really know the wiki so I'm not 100% on that)

//Magnus





--
Best regards,
Nikolay

---(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: [HACKERS] Loose ends in PG XML patch

[Nikolay Samokhvalov]

On 12/24/06, Tom Lane [EMAIL PROTECTED] wrote:

What I'm wondering about is why this printout is emitted as a separate
DEBUG message ... wouldn't it be better to incorporate it as the DETAIL
field of the error message?


Surely, it would. But the thing is that I couldn't manage to format
libxml2's native messages properly..



--
Best regards,
Nikolay

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] How to gain R/W access to developers wiki?

[Nikolay Samokhvalov]

So, can anybody help me?..

On 12/24/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:

Surely, I've created one (nickname: Nikolay) and are trying to do
things being logged in :-)

On 12/24/06, Magnus Hagander [EMAIL PROTECTED] wrote:
 Nikolay Samokhvalov wrote:
  I want to collect all XML todo issues (from my list, from Peter's and
  those Tom just described in Loose ends in PG XML patch) in one
  place, to make the overall picture clear for everyone.
  How can I gain access to developers wiki? (Actually, I've created the
  pageXML Todo - http://developer.postgresql.org/index.php/XML_Todo,
  but it's locked for editing).
 
 Not really knowing the internals of how the wiki is set up, I believe
 you just have to create an account and sign in with it to get write access.

 (Actually, I don't think you've created the page, I think you're just
 seeing the default placeholder page for something that's not created.
 But again, I don't really know the wiki so I'm not 100% on that)

 //Magnus




--
Best regards,
Nikolay




--
Best regards,
Nikolay

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] How to gain R/W access to developers wiki?

[Nikolay Samokhvalov]

As I already said, I try to edit pages being logged in (as Nikolay).

Now I've repeated the procedure:

1. I've created new account - samokhvalov.
2. I've confirm this account using URL that was mailed to me.
3. Ensured that I am logged in (see my nickname and the button log
out in the right upper corner).
4. Go to XML Todo page, click edit and see:

This page has been locked to prevent editing.

You can view and copy the source of this page

(actually, the same thing for any page)



On 12/24/06, Joshua D. Drake [EMAIL PROTECTED] wrote:

On Sun, 2006-12-24 at 20:45 +0300, Nikolay Samokhvalov wrote:
 So, can anybody help me?..

Moving to pgsql-www:

Just create an account:


http://developer.postgresql.org/index.php?title=Special:Userloginreturnto=XML_Todo



 On 12/24/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:
  Surely, I've created one (nickname: Nikolay) and are trying to do
  things being logged in :-)
 
  On 12/24/06, Magnus Hagander [EMAIL PROTECTED] wrote:
   Nikolay Samokhvalov wrote:
I want to collect all XML todo issues (from my list, from Peter's and
those Tom just described in Loose ends in PG XML patch) in one
place, to make the overall picture clear for everyone.
How can I gain access to developers wiki? (Actually, I've created the
pageXML Todo - http://developer.postgresql.org/index.php/XML_Todo,
but it's locked for editing).
   
   Not really knowing the internals of how the wiki is set up, I believe
   you just have to create an account and sign in with it to get write 
access.
  
   (Actually, I don't think you've created the page, I think you're just
   seeing the default placeholder page for something that's not created.
   But again, I don't really know the wiki so I'm not 100% on that)
  
   //Magnus
  
  
 
 
  --
  Best regards,
  Nikolay
 


--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate







--
Best regards,
Nikolay

---(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: [HACKERS] configure problem --with-libxml

[Nikolay Samokhvalov]

another way is:
export CPPFLAGS=$(xml2-config --cflags); ./configure --with-libxml

I think that such thing can be used in configure script itself,
overwise a lot of people will try, fail and do not use SQL/XML at all.

On 12/22/06, Pavel Stehule [EMAIL PROTECTED] wrote:

Hello,

I try to compile postgres with SQL/XML, but I finished on

checking libxml/parser.h usability... no
checking libxml/parser.h presence... no
checking for libxml/parser.h... no
configure: error: header file libxml/parser.h is required for XML support

I have Fedora Core 6, and libxml2-devel I have installed. I checked parser.h
and this file is in /usr/include/libxml2/libxml/ directory

I am sorry, but configure file is spenish vilage for me, and I can't correct
it.

Regards
Pavel Stehule

_
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org




--
Best regards,
Nikolay

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] xmlagg is not supported?

[Nikolay Samokhvalov]

Hmm... In my patch (http://chernowiki.ru/index.php?node=98) I didn't
remove this, moreover I've fixed a couple of issues...

Looks like it was removed by Peter (both patches he mailed lack it).

Actually, without this function a set is SQL/XML publishing functions
becomes rather poor.

Peter?

On 12/22/06, Pavel Stehule [EMAIL PROTECTED] wrote:

Hello,

why xmlagg is missing in SQL/XML support?

Regards
Pavel Stehule

_
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate




--
Best regards,
Nikolay

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] xmlagg is not supported?

[Nikolay Samokhvalov]

Another thing that was removed is XMLCOMMENT..

On 12/22/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:

Hmm... In my patch (http://chernowiki.ru/index.php?node=98) I didn't
remove this, moreover I've fixed a couple of issues...

Looks like it was removed by Peter (both patches he mailed lack it).

Actually, without this function a set is SQL/XML publishing functions
becomes rather poor.

Peter?

On 12/22/06, Pavel Stehule [EMAIL PROTECTED] wrote:
 Hello,

 why xmlagg is missing in SQL/XML support?

 Regards
 Pavel Stehule

 _
 Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
 http://www.msn.cz/


 ---(end of broadcast)---
 TIP 7: You can help support the PostgreSQL project by donating at

 http://www.postgresql.org/about/donate



--
Best regards,
Nikolay




--
Best regards,
Nikolay

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Proposal: syntax of operation with tsearch's configuration

[Nikolay Samokhvalov]

On 11/17/06, Peter Eisentraut [EMAIL PROTECTED] wrote:

Alvaro Herrera wrote:
 We should also take the opportunity to discuss new keywords for the
 XML support -- will we use new grammar, or functions?

The XML stuff is defined in the SQL standard and there are existing
implementations, so any nonstandard syntax is going to be significantly
less useful.  (The other problem is that you can't implement most of
the stuff in functions anyway.)


Yes, it's better not to mix XML syntax discussion and the Tsearch2
configuration syntax discussion in one place. Not only because these
are different things - here we have a discussion of syntax for catalog
manipulation commands, when XML stuff (at least that I was working on
during summer and am going to continue) is about functionality itself.
And in case of XML we have some things to stick to - the standard
papers and existent implementations...

However, Alvaro made me to recall my old thoughts - when I just
started to use Tsearch2 I was wondering why should I explicitly create
column for index - in other databases I shouldn't do this. Indeed,
this is the index and, ideally, all I have to do is to write CREATE
INDEX ... only, maybe with some custom (fulltext-special) additions
(and something like fulltext instead of gist).

So, is it possible to let people to avoid explicit ALTER TABLE .. ADD
COLUMN ... tsvector? Maybe it would be a syntax sugar too, but I
suppose that (especially for postgres-novices) it would simplify the
overall use of Tsearch. For me such changes are more important than
syntax for manipulations with catalog (i.e., I would live with insert
into ts_cfg ... one or two years more :-) ). However, I'm sure that
Oleg and Teodor already considered this feature and there should be
some things that prevent from letting users write only CREATE INDEX
w/o ALTERing tables...




I don't see any comparable arguments about this full-text search stuff.
In particular I don't see any arguments why a change would necessary at
all, including why moving to core would be necessary in the first
place.


Many hosters with PostgreSQL support (e.g. goDaddy - one of the
biggest hosters) don't provide any contrib module - so people have to
live w/o fulltext search. Then, many sysadmins are afraid of the word
contrib... So, there is no doubt for me that adding to core is
really good thing :-)

--
Best regards,
Nikolay

---(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: [HACKERS] Fwd: pg_dump VS alter database ... set search_path ...

[Nikolay Samokhvalov]

On 10/9/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:

Maybe my understanding is wrong - I'll be glad to hear why.


Maybe at least to create special switcher for database settings? (It
would remain backward compatibility...)

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] Fwd: pg_dump VS alter database ... set search_path ...

[Nikolay Samokhvalov]

What is the reason to not include database settings (like search_path)
to database dump created with pg_dump -C?
For me, I've created tmp patch for pg_dump to make my system work
(patch for CVS version is included).

-- Forwarded message --
From: Nikolay Samokhvalov [EMAIL PROTECTED]
Date: Oct 9, 2006 12:45 PM
Subject: pg_dump VS alter database ... set search_path ...
To: PostgreSQL-general pgsql-general@postgresql.org


Hi,

What is the best practice for following case:
In my database I have a set (~10) of schemas; my database periodically
is being backed up and restored at another machine. I have set up
search_path via ALTER DATABASE ... SET search_path TO ... to make
all needed schemas visible to any user who has appropriate rights. The
problem is that I cannot use pg_dumpall and pg_dump DOES NOT dump this
ALTER command, even being executed with -C option.

Using additional restoration script with list of schemas seems not the
best solution, because a set of schemas can be changed and I have not
only one database.

Search in mail archives gives me understanding that this issue is
among not resolved ones (there are pros and cons for including such
ALTER in pg_dump-ing).

Is there any common practice for this [probably very frequent] issue?

--
Best regards,
Nikolay


--
Best regards,
Nikolay


pg_dump_settings82b1.patch
Description: Binary data

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Fwd: pg_dump VS alter database ... set search_path ...

[Nikolay Samokhvalov]

On 10/9/06, Tom Lane [EMAIL PROTECTED] wrote:

Duplication of code and functionality with pg_dumpall.


Well, then -C option of pg_dump can be considered as duplication of
pg_dumpall's functionality too, right?


I'd want to see
some thought about how to resolve that, not just a quick copy-some-code-
from-pg_dumpall-into-pg_dump.  You also need to explain why this issue
should be treated differently from users and groups ...  a dump won't
restore correctly without that supporting context either.

I have no objection to rethinking the division of labor between the two
programs, but let's end up with something that's cleaner not uglier.


-C option is useful in cases like mine. Example: in a PG cluster of
100 databases there is one database containing 10 schemes; this
database is being dumped every night and restored on 3 separate
machines, where some operations are then being executed). pg_dumpall
is not a solution in this case. Moreover, playing with ALTER USER ...
SET search_path TO ...  may not the best solution too - there may be
different users sets on different hosts, and, what is more important,
if I (developing my app) add new schema to that database, I should run
number of hosts * number of roles ALTERs, this is not good.

When I write ALTER DATABASE ... SET ... I expect that corresponding
*database's* property will be modified. When I choose -C option of
pg_dump I expect that CREATE DATABASE with all its properties (in
ALTER stmts) will be printed. I think it's not a question of division
of labor between the two programs.

As for users and groups - I do not understand why you are mentioning
it. I'm talking about -C option, and complain that it doesn't allow
me to dump/restore the database with its properties. I suppose,
users/roles shouldn't be involved in this discussion.

Maybe my understanding is wrong - I'll be glad to hear why.

--
Best regards,
Nikolay

---(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: [HACKERS] [DOCS] New XML section for documentation

[Nikolay Samokhvalov]

On 8/26/06, Peter Eisentraut [EMAIL PROTECTED] wrote:

Bruce Momjian wrote:
 I made it clear in the section that the XML syntax was being checked,
 not validation against a schema.  You want Check and Validation
 sections?

Valid and well-formed have very specific distinct meanings in XML.
(Note that check doesn't have any meaning there.)  We will eventually
want a method to verify both the validity and the well-formedness.

I think that a function called xml_valid checks for well-formedness is
an outright bug and needs to be fixed.


That's exactly what I'm talking about. xml_valid() is wrong name and
it may confuse people.
I what to add that, with XML section in the documentation, this bug
becomes more significant.

Bruce suggested to use overload to keep backward compat. - in other
words, 1-arg function for checking for well-formedness and 2-arg
function for validation process. That's bad too:
- two _different_ actions for one function = another confusion
 - I (as a user) would think that 1-arg function is designed for
validation process for cases when XML document contains a reference to
DTD (as an example).

I stand for fixing it via renaming, breaking backward compatibility.
Later it will be more painful.

BTW, what is the deadline for changes (additions) in docs? I would add
general XML terms (such as what is XML, what is well-formed document,
what is validation; short overview of XML standards and SQL/XML as a
part of SQL:200n, etc Maybe about contrib/xml2 installation process -
actually, XSLT support requires additional lib). Moreover, if SQL/XML
patch will be accepted it will require several words too.
--
Best regards,
Nikolay

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] New XML section for documentation

[Nikolay Samokhvalov]

On 8/26/06, Peter Eisentraut [EMAIL PROTECTED] wrote:

Bruce Momjian wrote:
 Validation
 --
 /contrib/xml2 has a function called xml_valid() that can be used in
 a CHECK constraint to enforce that a field contains valid XML.  It
 does not support validation against a specific XML schema.

Then this is not validation but only checking for well-formedness.  The
xml2 README says so, in fact.


Exactly. contrib/xml2 mixes the term here, xml_valid() should be
another function, that takes two types of data - XML value and
corresponding XML schema - and validate the XML data. Actually, the
latest version of SQL/XML standard includes such a function
(XMLVALIDATE).

If you decide to include the mentioning about contrib/xml2 to docs, I
would suggest the patch for this module. The patch renames that
function to xml_check() and adds xml_array() (issue from the current
TODO). Or it's too late for 8.2?

Also, I would add a little introduction to XML terms (from XML
standards) to this documentation section.

--
Best regards,
Nikolay

---(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: [HACKERS] New XML section for documentation

[Nikolay Samokhvalov]

On 8/26/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:
[...]

If you decide to include the mentioning about contrib/xml2 to docs, I
would suggest the patch for this module. The patch renames that
function to xml_check() and adds xml_array() (issue from the current
TODO). Or it's too late for 8.2?

[...]

Typo :-( I mean xpath_array()

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[HACKERS] How to control the content of BKI files during installation process?

[Nikolay Samokhvalov]

Is there any way to control (enable/disable) some parts of BKI file?
(actually, I mean not BKI files themselves but their prototypes -
include/catalog/pg_*.h)

For example, I add some function to include/catalog/pg_proc.h and want
to have ability to enable/disable it during installation process
(e.g., with some configure's options such as --enable-my-proc). The
thing is that genbki.sh doesn't understand preprocessor instructions -
so, I cannot use #ifdef.

Maybe somebody has already encountered with this problem and invented
some workaround?

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 6: explain analyze is your friend

[HACKERS] Some questions to developers

[Nikolay Samokhvalov]

Hello,

Recently an interview with Marten Mickos (mysql's ceo) appeared in
russian IT news (eg http://citcity.ru/12776/). I'd like ask some
[similar] questions to PostgreSQL core developers and write an article
in Russian. Actually, I'm sure that there is a great lack of PG news
in our mass media. Moreover, it seems that Anniversary is a good
opportunity to rise a wave of interest.

--
Best regards,
Nikolay

---(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: [HACKERS] [SoC] Relation between project XML improvements and pgxml

[Nikolay Samokhvalov]

Yes, there is no single way, because there are different tasks. There
are many papers on this theme.

I'm pretty sure that first of all we need to analyze other DBMSes'
experience. I'm working on it, analyzing MS, ORA and DB2 (first
results of analysis will be available in several weeks).

I've submitted proposal 'XMLType for PostgreSQL' to Google SoC page
(my 'minimum' list from here:
http://archives.postgresql.org/pgsql-hackers/2006-05/msg00044.php

On 5/4/06, Jonah H. Harris [EMAIL PROTECTED] wrote:

On 5/3/06, Robert Staudinger [EMAIL PROTECTED] wrote:
 on your summer of code page [1] you outline a project XML improvements.
 Is there any relation (similar goals, feature overlapping, technical
 relation) to the pgxml project mentioned for some time on [2]?

No, the XML project idea submitted did not include Oleg's stuff at all.

 now that I'm pondering the submission of a
 SoC proposal I'm wondering if the XML improvements project is a
 completely new approach, maybe even superceding the
 approach outlined

At this point in time, I don't believe there's any single best way to
go regarding XML-handling in PostgreSQL.  If you have a neat project
idea, please propose it to us on Google's site!

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings




--
Best regards,
Nikolay

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] [SoC] Relation between project XML improvements and pgxml

[Nikolay Samokhvalov]

Actually, project mentioned on Oleg's page is only in plan.

I see some gap between current moment and the moment when GiST will
come to power for XML support - check out my proposal
(http://archives.postgresql.org/pgsql-hackers/2006-05/msg00044.php),
the 'minumum' list is that gap. When we will have some basic support
(ideally, according SQL:200n SQL/XML standard and based on experience
taken from commercial DBMSes), we would work on index support (w/o
which this project definitely won't be applicable to production
purposes) - first of all, path indexes and structure indexes. This
includes some labeling schema (probably prefix schema, see
http://davis.wpi.edu/dsrg/vamana/WebPages/Publication.html or papers
about MS' ORDPATHs). GiST and Gis will definitely help here a lot.

On 5/3/06, Robert Staudinger [EMAIL PROTECTED] wrote:

Hello,

on your summer of code page [1] you outline a project XML improvements.
Is there any relation (similar goals, feature overlapping, technical
relation) to the pgxml project mentioned for some time on [2]? I
have been (remotely) following  Oleg Bartunov's page on GiST usage and
datatypes for some time, now that I'm pondering the submission of a
SoC proposal I'm wondering if the XML improvements project is a
completely new approach, maybe even superceding the approach outlined
by Oleg.

[1] http://www.postgresql.org/developer/summerofcode
[2] http://www.sai.msu.su/~megera/postgres/gist/

Best regards,
Rob Staudinger

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq




--
Best regards,
Nikolay

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Google SoC--Idea Request

[Nikolay Samokhvalov]

Proposal: XMLType for PostgreSQL.

*** Minimum: ***
to have special type support for storing XML data and working with it.
This means following:
- ability to define any column of a table as of XMLType; internally,
all data is stored as VARCHAR;
- auto validation of documents against XML schema, if it was
specified in column
definition or in XML data sheets themselves (DTD, XSD or at least one
of them) /*contrib/xml2 has such feature, but it uses libxml, what
means DOM interface. Maybe it's better to use some SAX parser to solve
this task*/;
- XPath indexes for queries with path expressions in WHERE clause /*I
suppose this kind of indexes would be most frequently used. I propose
using good labeling schema and GIST and/or Gin here*/;
- some subset of SQL/XML. Actually, part 14 of SQL:200n (SQL/XML) has
more than 400 pages now and contains some established constructions,
that are using in other DBMSes. There is the some patch already
written by Pavel Stehule:
http://www.pgsql.ru/db/mw/msg.html?mid=2096818. (BTW, what is with it?
it was kept for 8.2, so what is the result?) I've tested it several
months ago, basic SQL/XML functions worked fine. It changes grammar,
but there is no other way... So, using this patch as a part of this
project means that this project cannot be contrib module,
unfortunately. Nevertheless, current paper of SQL/XML standard seems
to be mature - so, compared with existing implementation it would be a
nice 'landmark';
- XML domains support: ability to define domain based on XMLType and
XML schema definition (e.g., external DTD file or smth). I'd consider
XML schema definition as a restriction of entire XML Type (similar to
restrictions for plain types, which are defined as CHECK constraint in
domain definition)

*** Maximum: ***
- all things from 'minimum' list :-)
- reach index system:
 * structure index (labeling schema; prefix schemas seem to be best
for this and I
suppose GIST would help here). Actually, it would be full shredding,
like primary index for XML in MS SQL Server, but I'm aware of better
labeling algorithms than simple prefix labeling (as in SQL Server).
Surely, GIST/Gin support would be great foundation for these
 * flexible support of path indexes, value indexes and so on (smth
like secondary XML  indexes in SQL Server...) - as a continuation of
work on path indexes from 'minimum' list;
- full-text search abilties (tsearch2 / GIST);
- different encoding issues (auto conversion to column's encoding, etc);
- ability to choose storage type: VARCHAR or 'native' (trees - like
in native XML DBMSes and DB2 Viper [if their articles don't lie ;-)])
mode. Actually, this is very-very huge task (almost so as creating
DBMS from scratch) and I inderstand clearly that I won't solve it
using only my own abilities. But the work on 'minimum' list
(especially if it will be a part of SoC) would be a good start point
and may involve some other developers that help to implement it. Maybe
at the initial stage, it's worth to integrate with some other DBMS and
work with it using two-phase commit (surely, this is not a clue to all
problems, as it
means two different execution plans, etc);
- XQuery and its integration with SQL (according SQL/XML standard).
In other words,  implementation of XQuery Data Model - this would be
great target point (version 1.0 of entire  project);
- XML views / updatable XML views (actually, it's a crazy idea, but
it's my dream ;-) )

As a part of SoC I would concentrate on tasks from 'minimum' list. It
would be a good start point.

Some articles:
Fresh draft of SQL:200n: http://www.wiscorp.com/sql_2003_standard.zip
Other SQL/XML papers: http://www.wiscorp.com/SQLStandards.html#xsqlstandards
XISS system (Li, Moon - advanced interval indexes):
http://www.cs.arizona.edu/xiss/
MASS (prefix indexes):
http://davis.wpi.edu/dsrg/vamana/WebPages/Publication.html
Staircase joins (accelerating XPath Evaluation):
http://www.inf.uni-konstanz.de/dbis/publications/download/injection.pdf
Oleg's TODO list: http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo
XML in DB2 Viper: http://www.vldb2005.org/program/paper/thu/p1164-nicola.pdf
XQuery in SQL Server: http://www.vldb2005.org/program/paper/thu/p1175-pal.pdf
Labeling schema in SQL Server (ORDPATHs):
http://portal.acm.org/ft_gateway.cfm?id=1007686type=pdfcoll=GUIDEdl=GUIDECFID=74920272CFTOKEN=73736781

One more comment: I'm a PhD student of MIPT, Russia. I plan to create
an overview of XMLType implementations of last versions of three major
commercial DBMSes (ORA, MS, DB2), comparing them to standard and each
other. First article of this comparison is planned to the end of May.
This work will help to understand, where major commercial DBMS vendors
go and why they go there :-) Moreover, I intend to create a technique
for testing of XMLType support in (O)RDBMSes. In spite of the fact,
that SoC assumes all work be done by only one person, I expect some 
upport/help from following people:

- Dr. Sergey Kuznetsov (my scientific