Re: select limit error in file_fdw

On 2018-12-16 11:19, Erik Rijkers wrote:
> On 2018-12-16 07:03, Tom Lane wrote:
>> Erik Rijkers <er(at)xs4all(dot)nl> writes:
>>> I have noticed that since ffa4cbd623, a foreign table that pulls data
>>> from a PROGRAM (in this case an unzip call) will fail if there is a
>>> LIMIT on the SELECT
>>> (while succeeding without LIMIT). Below is an example.
>>
>> Um ... this example works for me, in both HEAD and v11 branch tip.
>> Moreover, the behavior you describe is exactly what ffa4cbd623 was
>> intended to fix. Is there any chance that you got 11.1 and v11
>> branch tip mixed up?
>
> I admit it's suspicious. I am assuming I pull the latest, from
> REL_11_STABLE, but I will have another hard look at my build stuff.

To circumvent a possible bug in my normal build stuff, I built
an instance from scratch, maybe someone could check for any errors
that I may have overlooked?

The instance built with this still has the LIMIT error.

I did notice that the error (as provoked by the earlier posted test.sh)
can be avoided by adding 'count(*) over ()' to the select list.
Not really surprising, I suppose.

Here is my scratch_build.sh:

------------
#!/bin/bash

git --version

project=scratch

# shutdown - just in case it's running
/home/aardvark/pg_stuff/pg_installations/pgsql.$project/bin.fast/pg_ctl
\
-D /home/aardvark/pg_stuff/pg_installations/pgsql.scratch/data \
-l logfile -w stop

cd ~/tmp/

# if [[ 0 -eq 1 ]]; then
git clone https://git.postgresql.org/git/postgresql.git
cd postgresql
git checkout REL_11_STABLE
# else
# cd postgresql
# # git pull
# fi

echo "deleting stuff"
make distclean &> /dev/null

echo "rebuilding stuff"
time ( ./configure \
--prefix=/home/aardvark/pg_stuff/pg_installations/pgsql.$project \

--bindir=/home/aardvark/pg_stuff/pg_installations/pgsql.$project/bin.fast
\

--libdir=/home/aardvark/pg_stuff/pg_installations/pgsql.$project/lib.fast
\
--with-pgport=6011 --quiet --enable-depend --with-openssl
--with-libxml \
--with-libxslt --with-zlib --enable-tap-tests \
--with-extra-version=_$project \
&& make -j 6 && ( cd contrib; make ) \
&& make check \
&& make install && ( cd contrib; make install ) \
&&
/home/aardvark/pg_stuff/pg_installations/pgsql.$project/bin.fast/initdb
\
-D /home/aardvark/pg_stuff/pg_installations/pgsql.$project/data -E
UTF8 \
-A scram-sha-256 --pwfile=/home/aardvark/pg_stuff/.11devel
--data-checksums \
--waldir=/home/aardvark/pg_stuff/pg_installations_wal/pgsql.$project
rc=$?
echo "rc [$rc]"
)

/home/aardvark/pg_stuff/pg_installations/pgsql.$project/bin.fast/pg_ctl
\
-D /home/aardvark/pg_stuff/pg_installations/pgsql.scratch/data \
-l logfile -w start

echo "select current_setting('port'), version()" \
|
/home/aardvark/pg_stuff/pg_installations/pgsql.$project/bin.fast/psql
-qX service=scratch
echo "
create extension file_fdw;
\\dx
" |
/home/aardvark/pg_stuff/pg_installations/pgsql.$project/bin.fast/psql
-qX service=scratch

/home/aardvark/pg_stuff/pg_installations/pgsql.$project/bin.fast/pg_ctl
\
-D /home/aardvark/pg_stuff/pg_installations/pgsql.scratch/data \
-l logfile -w stop

------------

comments welcome.

thanks,

Erik Rijkers

> On the other hand, in that test.sh, have you tried enlarging the test
> table? It works for me too with small enough values in that
> generate_series.
>
>> If not, there must be some platform-specific behavior involved.
>> What are you testing on, exactly?
>
> This is debian 9/Stretch:
>
> /etc/os-release:
> "Debian GNU/Linux 9 (stretch)"
>
> uname -a
> Linux gulo 4.9.0-8-amd64 #1 SMP Debian 4.9.130-2 (2018-10-27) x86_64
> GNU/Linux
>
> /proc/cpuinfo
> processor : 0
> vendor_id : GenuineIntel
> cpu family : 6
> model : 42
> model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
> stepping : 7
> microcode : 0x25
> cpu MHz : 2299.645
> cache size : 6144 KB
> physical id : 0
> siblings : 4
> core id : 0
> cpu cores : 4
> apicid : 0
> initial apicid : 0
> fpu : yes
> fpu_exception : yes
> cpuid level : 13
> wp : yes
> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
> mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe
> syscall nx rdtscp l
> bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass
> l1tf
> bogomips : 6185.58
> clflush size : 64
> cache_alignment : 64
> address sizes : 36 bits physical, 48 bits virtual
> power management:
>
>
> $ (PGSERVICE=dev11 psql -c "select version()")
> PostgreSQL 11.1_REL_11_STABLE_20181216_0458_171c on
> x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0
> 20170516, 64-bit
> (1 row)
>
> (note that '171c', tacked on via --with-extra-version)
>
>
> What other info could be useful?