Re: Windows installation problem at post-install step
Hi Thomas, This issue is seen only on v16 and not the back branches (tested on 15 and 14) and also confirmed by @Ertan Küçükoglu at https://github.com/EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442 On Fri, Aug 2, 2024 at 6:14 PM Sandeep Thakkar < sandeep.thak...@enterprisedb.com> wrote: > > > On Mon, Jul 22, 2024 at 6:10 PM Sandeep Thakkar < > sandeep.thak...@enterprisedb.com> wrote: > >> >> >> On Mon, Jul 22, 2024 at 5:52 PM Ertan Küçükoglu < >> ertan.kucuko...@gmail.com> wrote: >> >>> Sandeep Thakkar , 22 Tem 2024 Pzt, >>> 15:01 tarihinde şunu yazdı: >>> When I checked the installation log shared by Ertan, I do see that the locale passed to initcluster script is the same as returned by the getlocales executable. Executing C:\Windows\System32\cscript //NoLogo "C:\Program Files\PostgreSQL\16/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "" "C:\Users\User1\AppData\Local\Temp/postgresql_installer_cd79fad8b7" "C:\Program Files\PostgreSQL\16" "C:\DATA_PG16" 5432 "Turkish,Türkiye" 0 >>> >>> That is log file line no 5544 and is cscript logging. There is no >>> problem here. >>> If you check log file line no 5606 you will see that the encoding is not >>> correct just before initdb >>> Maybe this is related to BAT file usage? I don't know. >>> >>> Ah, I see it now. Let me take a closer look >> > > That line gets printed from within the initdb and is not printed by the > script. > @Thomas Munro I may be wrong but I guess fixing > the code might fix this. > Can you please share your patch so that I apply it on top of the last > released > source version, generate the installer and confirm the behaviour? > > >> >>> Thanks & Regards, >>> Ertan >>> >> >> >> -- >> Sandeep Thakkar >> >> >> > > -- > Sandeep Thakkar > > > -- Sandeep Thakkar
UPDATE-FROM and INNER-JOIN
In https://sqlite.org/forum/forumpost/df23d80682 Richard Hipp (Mr SQLite) shows an example of something that used to be supported by SQLite, but then wasn't, to be compatible with PostgreSQL. Thus I'm curious as to why PostgreSQL refuses the first formulation. Could anyone provide any insights? Thanks, --DD
Building v17 Beta2 on Windows
Hi. I'm using as the go-between for a colleague that's not subscribed to this ML. In short, we have Python-based automation scripts to build our 3rd parties. And the ones we have, that worked fine with v16, do not with v17 beta2, on Windows. Thus I'm asking for advice as how we should proceed. TIA, --DD PS: My colleague also mentioned finding posts about a change around a Meson build??? Is that now required? Any documentation / release notes regarding such a change? PPS: Our intent was to test the new wait-on-socket API discussed with Tom recently. 8< From my colleague I tried to build the Linux and Windows PostgreSQL 17beta2 devkits. For that I downloaded the source code from: https://www.postgresql.org/ftp/source/v17beta2/ And tried to use the python builders we have to build PostgreSQL devkits in an automated way. No problem on Linux platform, but the build on Windows platform failed. We used to proceed this way to build the Windows devkit using VS2022 In the PostgreSQL source folder: * Cd src\tools\msvc * Create a config.pl file containing a line to locate the OpenSSL devkits * Run: .\build.bat [DEBUG] The issue is that, in the PostgreSQL source dir installed from https://www.postgresql.org/ftp/source/v17beta2/ postgresql-17beta2.tar.gz, there is no src/tools/msvc folder. I looked for a Windows 17beta2 package to download, but only saw Postgresql 16 version: https://www.postgresql.org/docs/current/install-windows-full.html There is no mention of Windows OS. neither in https://www.postgresql.org/download/snapshots/ nor in https://www.postgresql.org/download/windows/ So for the moment, I don’t know how to proceed
Re: Building v17 Beta2 on Windows
On Mon, 5 Aug 2024 at 23:41, Dominique Devienne wrote: > Is that now required? Any documentation / release notes regarding > such a change? Yes. The release notes mention: "Remove the Microsoft Visual Studio-specific PostgreSQL build option (Michael Paquier) Meson is now the only available method for Visual Studio builds." See: https://www.postgresql.org/docs/17/release-17.html > I looked for a Windows 17beta2 package to download, but only saw > Postgresql 16 version: > https://www.postgresql.org/docs/current/install-windows-full.html https://www.postgresql.org/download/snapshots/ does link to https://www.enterprisedb.com/products-services-training/pgdevdownload . I see Windows installers for v17b2. > So for the moment, I don’t know how to proceed Meson is now the build system for Windows: https://www.postgresql.org/docs/17/install-meson.html David
Re: UPDATE-FROM and INNER-JOIN
On Monday, August 5, 2024, Dominique Devienne wrote: > In https://sqlite.org/forum/forumpost/df23d80682 > Richard Hipp (Mr SQLite) shows an example of something > that used to be supported by SQLite, but then wasn't, to be > compatible with PostgreSQL. > > Thus I'm curious as to why PostgreSQL refuses the first formulation. > Could anyone provide any insights? Thanks, --DD > Interesting…but not too surprising. The joining condition between the update relation and the from relation needs to be done in the where clause. You cannot reference columns of the update relation in the from clause because the update relation is not named in the from clause. There is still an underlying “why” here that I don’t know… David J.
Re: UPDATE-FROM and INNER-JOIN
Dominique Devienne writes: > In https://sqlite.org/forum/forumpost/df23d80682 > Richard Hipp (Mr SQLite) shows an example of something > that used to be supported by SQLite, but then wasn't, to be > compatible with PostgreSQL. For the archives' sake: CREATE TABLE t1(aa INT, bb INT); CREATE TABLE t2(mm INT, nn INT); CREATE TABLE t3(xx INT, yy INT); UPDATE t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa; yields ERROR: column "aa" does not exist LINE 1: ... t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa; ^ DETAIL: There is a column named "aa" in table "t1", but it cannot be referenced from this part of the query. > Thus I'm curious as to why PostgreSQL refuses the first formulation. > Could anyone provide any insights? Thanks, --DD This seems correct to me. The scope of the ON clause is just the relations within the INNER JOIN, which does not include t1. You would get the same from SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa; ERROR: column "aa" does not exist LINE 1: SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa; ^ because again t1 is not part of the JOIN sub-clause. (MySQL used to get this wrong, many years ago, and it seems that has taught a lot of people some strange ideas about syntactic precedence within FROM clauses. Postgres' behavior agrees with the SQL spec here.) regards, tom lane
Re: UPDATE-FROM and INNER-JOIN
On Mon, Aug 5, 2024 at 3:56 PM Tom Lane wrote: > Dominique Devienne writes: > > In https://sqlite.org/forum/forumpost/df23d80682 > > Richard Hipp (Mr SQLite) shows an example of something > > that used to be supported by SQLite, but then wasn't, to be > > compatible with PostgreSQL. > This seems correct to me. The scope of the ON clause is just > the relations within the INNER JOIN, which does not include t1. The [SQLite doc][1] does mention: 1) "With UPDATE-FROM you can join the target table against other tables" 2) "The target table is not included in the FROM clause, unless the intent is to do a self-join" which one can easily read as the update-target-table being implicitly part of the join, and thus OK to JOIN-ON against. Yes it is the SQLite doc, and not PostgreSQL's, but naively I tend to agree with the OP (on the SQLite Forum) that it "ought" to work. In both SQLite (as it used to), and in PostgreSQL. I'd rather SQLite and PostgreSQL continue to agree on this, but not in a restrictive way. Which would imply PostgreSQL accepting it (a tall order...). Or perhaps SQLite should allow it back. And PostgreSQL catch up eventually? The reason I find the restriction damaging is that `FROM t1, t2 WHERE t1.c1 = t2.c2` is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2 ON t1.c1 = t2.c2` which IMHO better separates "filtering" from "joining" columns. FWIW. Thanks, --DD [1]: https://www.sqlite.org/lang_update.html#update_from
Re: UPDATE-FROM and INNER-JOIN
On Mon, Aug 5, 2024 at 7:36 AM Dominique Devienne wrote: > I'd rather SQLite and PostgreSQL continue to agree on this, > but not in a restrictive way. I.e., you want to support the SQL Server syntax; allow the table named in UPDATE to be repeated, without an alias, in which case it is taken to represent the table being updated. And then allow the usual FROM clause to take form. Personally I get around this by simply doing: UPDATE tbl FROM (...) AS to_update WHERE tbl.id = to_update.id A bit more verbose in the typical case but the subquery in FROM can be separately executed during development then just plugged in. There is no noise in the outer where clause since its only purpose is to join the subquery to the table to be updated. The subquery has the full separation of filters from joins that one would like to have. David J.
Re: UPDATE-FROM and INNER-JOIN
Dominique Devienne writes: > The reason I find the restriction damaging is that `FROM t1, t2 WHERE > t1.c1 = t2.c2` > is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2 > ON t1.c1 = t2.c2` > which IMHO better separates "filtering" from "joining" columns. FWIW. But the whole point of that syntax is to be explicit about which tables the ON clause(s) can draw from. If we had a more complex FROM clause, with say three or four JOINs involved, which part of that would you argue the UPDATE target table should be implicitly inserted into? The only thing that would be non-ambiguous would be to require the target table to be explicitly named in FROM (and not treat that as a self-join, but as the sole scan of the target table). Some other RDBMSes do it like that, but it seems like too much of a compatibility break for us. Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE, so they offer no guidance. But I doubt we are going to change this unless the standard defines it and does so in a way that doesn't match what we're doing. regards, tom lane
Re: UPDATE-FROM and INNER-JOIN
On Mon, Aug 5, 2024 at 5:01 PM Tom Lane wrote: > > Dominique Devienne writes: > > The reason I find the restriction damaging is that `FROM t1, t2 WHERE > > t1.c1 = t2.c2` > > is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2 > > ON t1.c1 = t2.c2` > > which IMHO better separates "filtering" from "joining" columns. FWIW. > > But the whole point of that syntax is to be explicit about which > tables the ON clause(s) can draw from. If we had a more complex > FROM clause, with say three or four JOINs involved, which part of > that would you argue the UPDATE target table should be implicitly > inserted into? Wherever an update-target-column was referenced in an ON clause. Like SQLite used to support. I.e. possibly multiple times even, I guess. Yes that does imply the update-target table in not explicitly named in the FROM clause, specifically in the UPDATE-FROM case. Personally I don't find that "offensive", it's explicitly part of an UPDATE. > The only thing that would be non-ambiguous would > be to require the target table to be explicitly named in FROM > (and not treat that as a self-join, but as the sole scan of the > target table). Some other RDBMSes do it like that, but it seems > like too much of a compatibility break for us. The (old for now) SQLite way would be lifting a restriction, so that wouldn't be a backward incompatible change IMHO > Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE, > so they offer no guidance. And that's precisely why SQLite and PostgreSQL agreeing on a precedent would be nice. > But I doubt we are going to change > this unless the standard defines it and does so in a way that > doesn't match what we're doing. OK. Fair enough. I'm just expressing a personal opinion above. Which the SQLite Forum OP also supports I'd guess. FWIW. Thanks for your input Tom. --DD
Check psql parameter is passed, if not ask for it
Hi! I want to check if I pass the parameter "param1" to a sql script "delme.sql" like ``` \if ':param1' \echo :param1; \else \prompt 'Param1? ' :param1 \endif select :'param1'; ``` if parameter `param1` is not passed, the .sql should ask for it. If I run something like `psql -f delme.sql -v param1=1` it works, but if i pass `psql -f delme.sql -v param1=meh` i receive `psql:delme.sql:1: error: unrecognized value ":param1" for "\if expression": Boolean expected` How can I implement that? TIA, Wiwwo
Re: Check psql parameter is passed, if not ask for it
to quote the documentation:
> If an unquoted colon (:) followed by a psql variable name appears within
an argument, it is replaced by the variable's value, as described in SQL
Interpolation below.
> The forms :'variable_name' and :"variable_name" described there work as
well. The :{?variable_name} syntax allows testing whether a variable is
defined. It is
> substituted by TRUE or FALSE. Escaping the colon with a backslash
protects it from substitution.
See https://www.postgresql.org/docs/16/app-psql.html
On Mon, Aug 5, 2024 at 8:46 PM Wiwwo Staff wrote:
> Hi!
> I want to check if I pass the parameter "param1" to a sql script
> "delme.sql" like
> ```
> \if ':param1'
> \echo :param1;
> \else
> \prompt 'Param1? ' :param1
> \endif
> select :'param1';
> ```
> if parameter `param1` is not passed, the .sql should ask for it.
>
> If I run something like
> `psql -f delme.sql -v param1=1`
> it works, but if i pass
> `psql -f delme.sql -v param1=meh`
> i receive
> `psql:delme.sql:1: error: unrecognized value ":param1" for "\if
> expression": Boolean expected`
>
> How can I implement that?
> TIA, Wiwwo
>
Re: Windows installation problem at post-install step
On Mon, Aug 5, 2024 at 8:50 PM Sandeep Thakkar
wrote:
> This issue is seen only on v16 and not the back branches (tested on 15 and
> 14) and also confirmed by @Ertan Küçükoglu at
> https://github.com/EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442
Does that mean you can reproduce the problem with initdb.exe directly
in a shell? That is, remove the EDB installer from the picture and
compare v15 and v16 with the exact command line options that
initcluster.vbs is using, or perhaps just:
initdb.exe --locale="Turkish,Türkiye" --encoding=UTF-8 -D pgdata
. o O (Why does that locale name have a comma?) If v15 works and v16
breaks, perhaps you could try comparing the output with the attached
patch? It will give a hex dump of the contents of the locale name at
various points in the program, to see if/where it was corrupted, which
might also be a bit less confusing than looking at script output via
email (I don't even know how many onion layers of transcoding are
involved...)
From b97fe5a55e50a447d41a439412922ffe3f7e168b Mon Sep 17 00:00:00 2001
From: Thomas Munro
Date: Tue, 6 Aug 2024 16:06:29 +1200
Subject: [PATCH 1/3] xxx debug
---
src/bin/initdb/initdb.c | 34 +-
1 file changed, 33 insertions(+), 1 deletion(-)
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 458dc1137a3..abe5983ab16 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -336,6 +336,33 @@ do { \
output_failed = true, output_errno = errno; \
} while (0)
+static void
+xxx_debug(const char *prefix, const char *s)
+{
+ const unsigned char *us;
+
+ if (s == NULL)
+ {
+ printf("XXX debug: %s = NULL\n", prefix);
+ return;
+ }
+
+ printf("XXX debug raw: %s = \"%s\"\n", prefix, s);
+ printf("XXX debug hex: %s = { ", prefix);
+ for (us = (const unsigned char *) s; *us; us++)
+ printf("%02x ", *us);
+ printf("}\n");
+ printf("XXX debug txt: %s = { ", prefix);
+ for (us = (const unsigned char *) s; *us; us++)
+ {
+ if (*us >= 0x20 && *us < 0x80)
+ printf("%c ", *us);
+ else
+ printf("? ");
+ }
+ printf("}\n");
+}
+
/*
* Escape single quotes and backslashes, suitably for insertions into
* configuration files or SQL E'' strings.
@@ -2369,8 +2396,10 @@ setlocales(void)
* canonicalize locale names, and obtain any missing values from our
* current environment
*/
+ xxx_debug("setlocales lc_ctype", lc_ctype);
check_locale_name(LC_CTYPE, lc_ctype, &canonname);
lc_ctype = canonname;
+ xxx_debug("setlocales cannonname", lc_ctype);
check_locale_name(LC_COLLATE, lc_collate, &canonname);
lc_collate = canonname;
check_locale_name(LC_NUMERIC, lc_numeric, &canonname);
@@ -2597,7 +2626,10 @@ setup_locale_encoding(void)
strcmp(lc_ctype, lc_monetary) == 0 &&
strcmp(lc_ctype, lc_messages) == 0 &&
(!icu_locale || strcmp(lc_ctype, icu_locale) == 0))
+ {
+ xxx_debug("setup_locale_encoding", lc_ctype);
printf(_("The database cluster will be initialized with locale \"%s\".\n"), lc_ctype);
+ }
else
{
printf(_("The database cluster will be initialized with this locale configuration:\n"));
@@ -3056,7 +3088,6 @@ initialize_data_directory(void)
check_ok();
}
-
int
main(int argc, char *argv[])
{
@@ -3214,6 +3245,7 @@ main(int argc, char *argv[])
break;
case 1:
locale = pg_strdup(optarg);
+xxx_debug("getopt optarg", locale);
break;
case 2:
lc_collate = pg_strdup(optarg);
--
2.46.0
